I have a sheet where I would like to turn a row a color based on the value of two cells. I already have conditional formatting based on one cell of the cells I want to use for the two cell formatting. I am using =AND($J:$J="Cancel",$L:$L="Yes") for the two cell formatting but it doesnt seem to work. Not sure if the first one =$J:$J="Cancel" is negating the formatting of the other or if if my formula is just bad. Any advice would be appreciated.
4
votes
I came up with a work around that doesnt rely on the formatting. But I appreciate your response and will look at that in the future. I am sure I will run into it later. I haven't considered the order that they were added would have an impact, I am so used to using crm logic that I just assumed it would carry over. So your saying if one of the conditions might have no formatting based on the criteria it might be better to add that first, or else the first one will take priority?
- Eric
1 Answers
3
votes
if the trick is that you want the whole row to be colored that way, then all you need to modify is the "range" to apply it too, so you enter something like the start column and then just give it a row number as the second half of the range, without the column argument: A1:10001
That exact formula you listed =AND($J:$J="Cancel",$L:$L="Yes") worked for me when using the "custom formula" option:
