VBA cell color

[npacker]

Can I call and change cell colors in VBA? I would like some code in the "ThisWorkbook" sub to say something like

If Range("C7").color = "red" Then
Range("D7").color = "red"

or something to that effect. Any help?
Thanks,
Nate

 

[Smitty]

How about:

<font face=Tahoma>    <SPAN style="color:#00007F">If</SPAN> [C7].Interior.ColorIndex = 3 <SPAN style="color:#00007F">Then</SPAN> [D7].Interior.ColorIndex = 3</FONT>

HTH,

Smitty

 

[npacker]

That will work, do you know where I can find the color codes for the different colors?
Thanks,
Nate

 

[Norie]

Yes.

Something like this maybe:

If Range("C7").Interior.ColorIndex = 3 Then
        Range("D7") = "red"
End If

BTW you might not need VBA. In your other post you seem to be using Conditional Formatting for the colouring.

You could use the same conditions to return 'red', 'green' etc.

 

[Oaktree]

Hey Smitty!

You could also do

If Range("C7").Interior.Color = vbRed Then Range("D7").Interior.Color = vbRed

 

[npacker]

Thanks, guys, this ought to work. I am using conditional formatting, but I also need it to change the text of the cell, and I can't do that in conditional formatting.
Thanks,
Nate

 

[Norie]

Nate

You can use the same conditions you are using for the formatting to return a text value using logical worksheet functions like IF, AND etc

 

[npacker]

True, although my worksheet and conditions are probably going to be too complicatied to do that. I like the code idea, but how or where do I put the code to have it always be checking for it, so that when any of the colors in those cells change, it automatically updates?
Thanks,
Nate

 

[Smitty]

I believe that Erik van Geit gets the credit for this:

<font face=Tahoma><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> GetColours()
    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">For</SPAN> x = 1 <SPAN style="color:#00007F">To</SPAN> 56
            Cells(x, 1) = x
            Cells(x, 2).Interior.ColorIndex = x
        <SPAN style="color:#00007F">Next</SPAN> x
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Smitty

 

[npacker]

I think I see the process behind this code, but could someone explain it to me, cause I'm not sure I know exactly how it's working, or how to implement it.
Thanks,
Nate