CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
Brian
Brian

Reputation: 197

Conditional formatting a subset of cells based on a formula

I had thought that my question here:

In a subset of cells, determine if at least one corresponding cell matches a value

would give me enough information that I could apply it to conditional formatting, but that is not the case. So, my follow-up question is this: given the following data, I would like to conditionally format the cells in column A if, say, their value is "SR" AND there is not at least one corresponding cell in column B that contains "*". By "corresponding cell" I mean the cell immediately to the right of an "SR".

     A     B
1    DR    *
2    DR    &
3    SR    &
4    DR    *
5    SR    #
6    SR    #

In the above example data, cells A3, A5 and A6 should be conditionally formatted because there is not an "*" to the right of any of those three cells. Cells A1, A2 and A4 should not be conditionally formatted because they do not contain "SR".

Upvotes: 1

Views: 561

Answers (1)

Aditya Pansare
Aditya Pansare

Reputation: 1132

Use this formula in conditional formatting

=AND(A1="SR",COUNTIFS(A:A,A1,B:B,"~*")=0)

Upvotes: 1

Related Questions