I am trying to create a rule for conditional format. I have a row in which each cell has a range of 0-4 and hilights properly per the number its at, but I also need a cell to change color when any of the cells in that row are between 1&4, and go back to no highlight when all the cells in that row are 0 or 4.
0
votes
Are we talking all integers or are decimals included? A quick 1 row example would also be useful.
– James
Ok, so I'm trying to format A4 so that when cells B4-Z4 have a 1,2 or 3 entered in them cell A4 highlights. no decimals.
– user3075129
also, cells b4-z4 would only have one cell with anything other than a 0 or 4 at a time.
– user3075129
1 Answers
1
votes
Use the "Use a Formula..." option on the cell you want to format, and type the following:
=OR(NOT(ISERROR(HLOOKUP(2,B4:Z4,1,FALSE))),NOT(ISERROR(HLOOKUP(3,B4:Z4,1,FALSE))),NOT(ISERROR(HLOOKUP(3,B4:Z4,1,FALSE))))
This should evaluate as TRUE when it equals 1-3, and be unformatted otherwise.
It's made of a couple of basic principles:
HLOOKUP() will search for the number. It returns an error if it can't find one.
NOT(ISERROR()) will evaluate the HLOOKUP result as TRUE when the result is NOT an error, i.e. when the number is found in the range.
OR() then compiles three searches for the numbers 1-3, and returns TRUE if any one of them is found.
The formula will cause the cell to format when the final result is TRUE.
Hope this helps!
