[Solved] MATCH function in Conditional Formatting

[Twiggy]

Hi, I'm trying to conditional format a range of cells. If you would care to download the attached document, I want to format the cells in H4:M56 only if they match any of the values in D6:d57. I can do this in MS Excel using a formula like =MATCH(H4,$D$6:$D$57,0) and it works fine, but the font and size of text is very limited with MS Excel, thus I wish to use AOO. I simply cannot seem to get the syntax correct I suspect. Thank you very much indeed for any advice or solutions.

[Villeroy]

ISNUMBER(MATCH(H4;$D$6:$D$57;0))

[Lupp]

The errors causing an unexpected behaviour were two:
-1- The formula used for the condition contained the comma as the supposed parameter delimiter. In AOO you have to use the semicolon in this place.
-2- The conditionally assigned cell style was 'Default'.

Since 'Calc' handles any numeric value different from 0 as TRUE if it is to interpret in 'logical mode', and errors are ignored by CF (just being NOT TRUE), the ISNUMBER() envelope Villeroy suggested is not strictly necessary. It helps to get things clear however.

There is, hovever another issue with CF in AOO: The relative address to use in the conditions for any single cell of the range for which CF is edited depends on the way you selected the range:
If you did it dragging with the mouse or 'Shift'+'CursorKeys' from bottom- right to top-left the "active cell" defining the relative address to use is leftmost-topmost in the range. The same "active cell" you get if you enter the range address into the 'Name Box'.
If you created the selection by a different dragging, you need to use the cell where you released the mouse botton or stopped with 'Shift'+'CursorKey', as the base for relative addressing.

[gerard24]

This can also be done with COUNTIF($D$6:$D$57;H4)

[Villeroy]

This can also be done with COUNTIF($D$6:$D$57;H4)

Absolutely. However, both MATCH and COUNTIF may suffer from Calc's calculation options.
Tools>Options>Calc>Calculation...
Regular expressions in formulas = OFF (unless you really know that you want this and how to use it)
Match whole cell = depends on if you want MATCH or COUNTIF match/count a, b, c, ab or bc in abc
Both are per-document options. My personal default template has regular expressions OFF and match whole cell ON.

[Twiggy]

That works great!! Thank you guys I'll give you all Kudos but the one that nailed it was using ISNUMBER function. My main issue was the limits using MS Excel. Can't change text size, font, colour etc. And changing : to ; would also help. One happy bunny Even saved it as ODS and opened in MS Excel and exactly what I want!

[Lupp]

When I drafted my answer I had access to an attached example 'Fox and Hounds September.ods'. Where is this attachment gone? Without it my answer may seem somehow dumb while it was very precise and thorough. I don't exactly like that.

@Twiggy edited the OQ on 2017-09-16 04:49 (forum time? CEST?) once more after the last post made here (except this one, of course). Most likely the attachment was deleted on this occasion. I don't consider this good practice.

[Twiggy]

When I drafted my answer I had access to an attached example 'Fox and Hounds September.ods'. Where is this attachment gone? Without it my answer may seem somehow dumb while it was very precise and thorough. I don't exactly like that.

@Twiggy edited the OQ on 2017-09-16 04:49 (forum time? CEST?) once more after the last post made here (except this one, of course). Most likely the attachment was deleted on this occasion. I don't consider this good practice.

Lupp - I don't really see your issue with my posts. I have thanked you all. I have also not knowingly deleted the attachment.I have only edited the original post to put "[Solved] in the Subject field - which is good practice.

[RoryOF]

You and Forum Administrators/Moderators are the only people with access to your posts; I am confident that the Admins/Mods did not delete your attachment - we normal will only delete attachments for reasons of content confidentiality or (very rarely) for objectionable content. It may be that in your editing of the first post, which contained the attachment, you changed some setting which resulted in the deletion.

[Twiggy]

There were names in the attachment. But no indication of address or location. Perhaps in hindsight I should have submitted a small, general sample of what I was trying to resolve. However, I do take your point that it was very unlikely that Admin would have deleted it. Thank you very much indeed

[Lupp]

No big thing! I just wanted to state my opinion that retroactive changes in posts may be problematic, in specific if a later post expressly refers to its contents or to the contents of an original attachment. My answer did (point -2- mainly).

There were names in the attachment. But no indication of address or location. Perhaps in hindsight I should have submitted a small, general sample of what I was trying to resolve.

No ned of much ado in this simple case. The attachment was small enough. Anonymisation was simple and sufficient, I assume. (See new attachment. The downloaded original was still stored on my computer.)

Fox_and_Hounds_September_anonymised.ods

(31.23 KiB) Downloaded 121 times

@RoryOF: Sorry that I probably caused unnecessary trouble. If you advise me with this respect I will try to change my behaviour. I will also delete the attachment again if you suggest to do so.

[RoryOF]

No, Lupp: yours was a fair comment, as a missing attachment of a problem file renders much of the subsequent discussion (and hopefully, faultfinding) meaningless.