Hello!
So I have different (non-numeric) values in Column B (B3:B). And I have transactions (formatted as currency) in Columns C through E (range C3:E). So I want every cell in any given row within that range to be colored, whenever that cell has some value (non-empty) AND the corresponding cell (of the same row) in Column B has "$" value in it.
In other words I need a formula that would check the whole (C3:E) range, find all non-empty cells (assuming that there will be blank cells in that range), check the corresponding (of the same row number) cells in Column B and, if those corresponding cells contain "$" value, color all the non-empty cells in that row (even if there are several non-empty cells in there) within the (C3:E) range.
Let's say we see that in row 7 there are two cells (and they are within our C3:E range) that have values 23.54 and 67.90 (non-empty). That's the first condition. And we also see that the value of the corresponding cell in row 7 of Column B (B7) is "$". That's the second condition. So only cells C7 and E7 (not the whole C7-E7 row) must be colored by conditional formatting. So in the table I provided only cells D4, C7 and E7 must be colored.
That's the custom formula for Conditional Formatting that I came up with =AND(NOT(ISBLANK(C3:E)),AND($B3="$"))
but the problem is that it colors the whole row instead of the non-empty cells in that row only.
Where am I making a mistake?
Please advise.
Column B Column C Column D Column E
3 None 14.50 12.00
4 $ 45.90
5 Some 23.90
6 Few
7 $ 23.54 67.90
P.S: What would the formula be to color the cells that contain anything else but "$" with another color?