Search the Elsmar Cove!
**Search ALL of Elsmar.com** with DuckDuckGo including content not in the forum - Search results with No ads.

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

Darius

Quite Involved in Discussions
#11
Don't worry, we are here to help on whatever we can

The code must be on the sheets code, not in a module, because in the sheets code extend the internal methods that are triggered on every sheet whenever an event happen. This topic is fascinating but little is explained on the books. It reminds me the polymorphism in object programing.

Code:
Dim OLD_SELECTION As Range
Const GREEN = 5287936
Const RED = 255
Const YELLOW = 65535

Private Sub Worksheet_Change(ByVal Target As Range)
Dim CL As Range, RW As Integer, RWS As Integer
  RWS = Application.WorksheetFunction.CountA(Range("A:A"))
  For RW = 2 To RWS
    Set CL = Cells(RW, 12)
    Select Case (Cells(RW, 11).Interior.Color)
      Case (RED): If CL.Value <> 2 Then CL.Value = 2
      Case (YELLOW): If CL.Value <> 1 Then CL.Value = 1
      Case (GREEN): If (CL.Value <> 0) Or (CL.Value = "") Then CL.Value = 0
    End Select
  Next RW
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CL As Range
If OLD_SELECTION Is Nothing Then Set OLD_SELECTION = Target

If Range(OLD_SELECTION.Address).Column = 11 Then
  Set CL = Cells(OLD_SELECTION.Row, Range(OLD_SELECTION.Address).Column + 1)
  Select Case (OLD_SELECTION.Interior.Color)
    Case (RED): If CL.Value <> 2 Then CL.Value = 2
    Case (YELLOW): If CL.Value <> 1 Then CL.Value = 1
    Case (GREEN): If (CL.Value <> 0) Or (CL.Value = "") Then CL.Value = 0
  End Select
End If
Set OLD_SELECTION = Target
End Sub
I will try to get more frequently.
 

Attachments

#12
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 red if any of the cells on the hyperlinked sheet is red

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Range("B4").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
 
Top Bottom