VBA cell color

npacker

Board Regular
Joined
Oct 14, 2004
Messages
132
Can I call and change cell colors in VBA? I would like some code in the "ThisWorkbook" sub to say something like
Code:
If Range("C7").color = "red" Then
Range("D7").color = "red"
or something to that effect. Any help?
Thanks,
Nate
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

Board Regular
Joined
Oct 14, 2004
Messages
132
That will work, do you know where I can find the color codes for the different colors?
Thanks,
Nate
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
Yes.

Something like this maybe:
Code:
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365

ADVERTISEMENT

Hey Smitty!

You could also do

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

npacker

Board Regular
Joined
Oct 14, 2004
Messages
132
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Oct 14, 2004
Messages
132
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

Legend
Joined
May 15, 2003
Messages
29,536
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

Board Regular
Joined
Oct 14, 2004
Messages
132
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
 

Forum statistics

Threads
1,147,677
Messages
5,742,566
Members
423,738
Latest member
AshleyKitsune

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top