Conditional highlight based on values from multiple cells

gogumakilla

New Member
Joined
Jun 28, 2016
Messages
6
I have grouping of cells (apples, oranges, or bananas) in column A that I want to highlight ONLY IF all of the corresponding cells in column B say "Active" for that particular fruit grouping. IE the apple and bananas grouping would not be highlighted because they have both the "Active" and "Inactive" values in their grouping. The Oranges grouping is highlighted because all of its corresponding cells in column B says "Active". Is there a formula that can do this?

I probably explained this poorly but any help would be greatly appreciated, thanks.

29HI3RG.png
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Welcome to the forum.

Select the column(s) you want to highlight. Click Conditional Formatting > New Rule > Use a Formula. Enter:

=COUNTIF($A:$A,$A1)=COUNTIFS($A$A,$A1,$B$B,"Active")

Click Formatting and choose a fill color.

Let me know if this works.
 
Upvote 0
Welcome to the forum.

Select the column(s) you want to highlight. Click Conditional Formatting > New Rule > Use a Formula. Enter:

=COUNTIF($A:$A,$A1)=COUNTIFS($A$A,$A1,$B$B,"Active")

Click Formatting and choose a fill color.

Let me know if this works.

Thanks, Eric. Unfortunately I got an error on the formula.
 
Upvote 0
Sorry, I typoed 2 colons. Try this:

=AND(COUNTIF($A:$A,$A1)=COUNTIFS($A:$A,$A1,$B:$B,"Active"),A1<>"")

This also will not highlight cells that are empty.
 
Upvote 0
So my excel sheet is massive so the conditional formatting is clogging up all my resources...is there any way I can remove the CF but still keep the highlights?
 
Upvote 0
Not really. Best I can suggest is to write a VBA macro to perform the same function. Let me know if that's something you'd like.
 
Upvote 0
Try this:

1) Open your sheet
2) Remove the Conditional Formatting
3) Right click on the sheet tab on the bottom and select View Code
4) From the menu, select Insert > Module
5) Paste the following code:
Rich (BB code):
Public Sub DoColor()
Dim Col1 As Range, Col2 As Range, MyMax As Long, Val1, Val2, r As Long

    Set Col1 = Range("A:A")
    Set Col2 = Range("B:B")
    
    Union(Col1, Col2).Interior.ColorIndex = 0
    MyMax = Cells(Rows.Count, Col1.Column).End(xlUp).Row
    
    Val1 = Range(Cells(1, Col1.Column), Cells(MyMax, Col1.Column)).Value
    Val2 = Range(Cells(1, Col2.Column), Cells(MyMax, Col2.Column)).Value
    
    For r = 1 To MyMax
        If Application.WorksheetFunction.CountIf(Col1, Val1(r, 1)) = _
           Application.WorksheetFunction.CountIfs(Col1, Val1(r, 1), Col2, "Active") Then
           Cells(r, Col1.Column).Interior.ColorIndex = 4
           Cells(r, Col2.Column).Interior.ColorIndex = 4
        End If
    Next r
           
End Sub
6) Make sure the columns (in red) are what you want. The number in orange indicates the number of the colorindex to use in highlighting the cells. Excel has a default list of 56 colors. 1-8 are:
black, white, red, green, blue, yellow, magenta, cyan. You can also try 9-56 if you want. And if you still can find a color you like, we can get fancier.
7) Press Alt-Q to close the VBA editor
8) Press Alt-F8 to open the macro selector
9) Select DoColors and click Run.

I use the same algorithm to decide which cells to highlight, so I suspect it could be more efficient. But since it's an on-demand routine, it doesn't matter as much. You can rerun it whenever the data changes.

Hope this helps.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top