Home

How do I autofill cell colour based on other cells' colour in Excel?

%3CLINGO-SUB%20id%3D%22lingo-sub-530787%22%20slang%3D%22en-US%22%3EHow%20do%20I%20autofill%20cell%20colour%20based%20on%20other%20cells'%20colour%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-530787%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20created%20a%20spreadsheet%20that%20has%20multiple%20cells%20that%20go%20red%20based%20on%20conditional%20formatting.%20I%20have%20another%20sheet%20that%20has%20hyperlink%20cells%20to%20the%20other%20sheets%2C%20can%20I%20get%20these%20hyperlink%20cells%20to%20go%20green%20if%20all%20cells%20on%20that%20sheet%20are%20green%20then%20red%20if%20any%20of%20the%20cells%20on%20the%20hyperlinked%20sheet%20is%20red%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei've%20tried%20various%20things%20to%20get%20cells%20to%20change%20colour%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_SelectionChange(ByVal%20Target%20As%20Range)%3C%2FP%3E%3CP%3EMe.Range(%22B3%22).Interior.Color%20%3D%20Me.Range(%22A1%22).DisplayFormat.Interior.Color%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethis%20works%20on%20the%20same%20sheet%20but%20as%20soon%20as%20you%20introduce%20multiple%20cells%20it%20turns%20black%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_SelectionChange(ByVal%20Target%20As%20Range)%3C%2FP%3E%3CP%3EMe.Range(%22B4%22).Interior.Color%20%3D%20Me.Range(%22A1%3AB1%3AC1%22).DisplayFormat.Interior.Color%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eor%20cells%20from%20another%20sheet%20it%20fails%20error%20code%201004%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_SelectionChange(ByVal%20Target%20As%20Range)%3C%2FP%3E%3CP%3EMe.Range(%22B4%22).Interior.Color%20%3D%20Me.Range(%22Greenbrook!%24C%243%22).DisplayFormat.Interior.Color%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20this%20across%20sheet%2C%20this%20is%20for%20a%20single%20cell%2C%20I'm%20hoping%20to%20be%20able%20to%20look%20at%20all%20cells%20on%20the%20said%20sheet%20and%20show%20red%20if%20any%20cell%20on%20the%20other%20sheet%20are%20red%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-530787%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Ade_Salmon
Occasional Contributor

I have created a spreadsheet that has multiple cells that go red based on conditional formatting. I have another sheet that has hyperlink cells to the other sheets, can I get these hyperlink cells to go green if all cells on that sheet are green then red if any of the cells on the hyperlinked sheet is red

 

i've tried various things to get cells to change colour

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Me.Range("B3").Interior.Color = Me.Range("A1").DisplayFormat.Interior.Color

End Sub

 

this works on the same sheet but as soon as you introduce multiple cells it turns black

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Me.Range("B4").Interior.Color = Me.Range("A1:B1:C1").DisplayFormat.Interior.Color

End Sub

 

or cells from another sheet it fails error code 1004

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Me.Range("B4").Interior.Color = Me.Range("Greenbrook!$C$3").DisplayFormat.Interior.Color

End Sub

 

I tried this across sheet, this is for a single cell, I'm hoping to be able to look at all cells on the said sheet and show red if any cell on the other sheet are red

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies