Coloring a cell based on another cell's color

[MSPaperclipMan]

Hello,

I would like to color a cell based on another cell's color. However, I am running into an issue. I have tried the code shown below, and it does work to solve the question in hand. However, it stops working when I try to use the code with a cell which is "colored" by conditional formatting. Specifically, I am trying to color cell g25 if cell d25 shows as yellow. By appearance, cell d25 does look yellow (from the conditional formatting) but for some reason the code/excel doesn't "see" this and thinks the cell has no fill, and hence won't color cell g25 yellow as well. Only when I manually fill d25 with yellow does the code execute. Does anyone know a way around this? At the core, I am trying to color fill a cell based on another cell's color which will be filled with conditional formatting. I hope this made sense.

[Sub mycode()

If range("d25").Interior.ColorIndex = 6 Then

range("g25").Interior.ColorIndex = 6

End If

End Sub]

 

[Fluff]

Hi & welcome to MrExcel.
To detect conditional formatting you need to use

range("d25").DisplayFormat.Interior.ColorIndex = 6

 

[MSPaperclipMan]

Hi & welcome to MrExcel.
To detect conditional formatting you need to use

range("d25").DisplayFormat.Interior.ColorIndex = 6

Thanks for the welcome and response! I believe it worked. Do you happen to know how to apply this to a range of cells? e.g. (d25:d387)? I have the code below but it doesn't seem to want to apply this to the cells in the range typed.

Sub mycode()

If range("d25:d387").DisplayFormat.Interior.ColorIndex = 6 Then

range("h25:h387").Interior.ColorIndex = 6

Else: range("h25:h387").DisplayFormat.Interior.ColorIndex = 0

End If

End Sub

 

[Fluff]

You would need to loop through all the cells.

 

[MSPaperclipMan]

Thanks for the welcome and response! I believe it worked. Do you happen to know how to apply this to a range of cells? e.g. (d25:d387)? I have the code below but it doesn't seem to want to apply this to the cells in the range typed.

Sub mycode()

If range("d25:d387").DisplayFormat.Interior.ColorIndex = 6 Then

range("h25:h387").Interior.ColorIndex = 6

Else: range("h25:h387").DisplayFormat.Interior.ColorIndex = 0

End If

End Sub

And I am also trying to have the cells not get filled if it "sees" no color.

 

[MSPaperclipMan]

You would need to loop through all the cells.

Can you show me how to do this? Basically I have two columns of data. I just want column B to fill in a color based on the display color of column A.

 

[Fluff]

How about

Sub MSPaperclipMan()
   Dim Cl As Range
   
   For Each Cl In Range("D25:D387")
      If Cl.DisplayFormat.Interior.ColorIndex = 6 Then
         Cl.Offset(, 4).Interior.ColorIndex = 6
      Else
         Cl.Offset(, 4).Interior.ColorIndex = xlNone
      End If
   Next Cl
End Sub

 

[MSPaperclipMan]

How about

Sub MSPaperclipMan()
   Dim Cl As Range
  
   For Each Cl In Range("D25:D387")
      If Cl.DisplayFormat.Interior.ColorIndex = 6 Then
         Cl.Offset(, 4).Interior.ColorIndex = 6
      Else
         Cl.Offset(, 4).Interior.ColorIndex = xlNone
      End If
   Next Cl
End Sub

IT WORKED!!!!! I am over the moon right now. Oh man, it worked. Thank you very much, this was a huge step forward.

I wonder, can this work also to fill in a color for an associated value. Do you think this modified code would work?

Sub ColorCodebySDO()
Dim Cl As range

For Each Cl In range("h25:h387")
If C1 = SDO Then
Cl.Interior.ColorIndex = 6
Else
Cl.Interior.ColorIndex = xlNone
End If
Next Cl
End Sub

 

[Fluff]

If SDO is a text string in the cell then it needs to be wrapped in quotes, otherwise that should be fine.