Update font color of other cells in range

[dcoledc]

I am trying to make a range of cells change font color when one of them is changed. Initially, I have all the cells as red. If one of the cells is changed to blue, I want all the cells to change to blue. This is what I have tried to no avail:

Dim oCell as Range
Dim FontColor as String
For Each oCell in Range("D5:d11")
[INDENT]If oCell.Font.Color <> vbRed then
FontColor = oCell.Font.Color
oCell.Font.Color = FontColor
[/INDENT]End if
Next

I am fairly new to VBA and certainly new to looping. I feel this is something really small. Any help?

 

[VoG]

Try

Sub test()
Dim oCell As Range
For Each oCell In Range("D5:d11")
    If oCell.Font.Color <> vbRed Then
        Range("D5:D11").Font.Color = vbBlue
        Exit For
    End If
Next
End Sub

 

[dcoledc]

Thank you for the qucik response. I was actually just about to try and delete this thread as I came up with the solution. I do not necessarily want the cells to be blue. I want to be able to pick any color. Here is the solution I came up with:

Dim oCell as Range
Dim oCell2 as Range
Dim FontColor as String
 
For Each oCell In Range("D5:D11")
[INDENT]If oCell.Font.Color <> vbRed Then
[INDENT]FontColor = oCell.Font.Color
[/INDENT]End if
Next
[/INDENT]For Each oCell2 in Range("D5:D11")
[INDENT]Ocell2.Font.Color = FontColor
[/INDENT]Next

Thanks

 

[dcoledc]

Okay, so I thought I had it solved. My code will chagne all the fonts to whatever color I want, but it will only do it once. Once I try to change the colors again, they will all revert back to the last color. So if the last color was green and then I select orange, they all go back to green.

I feel I need to set a variable for the initial color. Instead of saying if <> vbRed, I need to say if <> to some variable. I am not sure how to do that though.

Any thoughts?

 

[VoG]

You can save the reference color in a cell (I used A1). Before running for the first time set A1 font color to the starting font color in your range. Then try changing one of the colors in the range and run the macro

Sub test()
Dim oCell As Range
Dim FontColor As Long
FontColor = Range("A1").Font.Color
For Each oCell In Range("D5:D11")
    If oCell.Font.Color <> FontColor Then
        Range("A1,D5:D11").Font.Color = oCell.Font.Color
        Exit For
    End If
Next
End Sub

 

[dcoledc]

Perfect! I am not familiar with this the way you placed a comma after A1 in the below code.

Range("A1,D5:D11").Font.Color = oCell.Font.Color

Is that how you set the reference cell or something?

Thanks again.

 

[VoG]

That just saves having two lines of code - one to set the font color of the reference cell and another to set the font color of the range in column D.

To see how that works try running this:

Sub bbb()
Range("A1,D5:D11").Select
End Sub

 

[dcoledc]

Thank you.