Global cell color change to another color

Gustavo

New Member
Joined
Jun 13, 2008
Messages
4
Does anyone know how to change a cell color to another color in a whole spreadsheet using VBA?

I have the cell color hexadecimal and RGB color values. Right now I have cells all over that are yellow and I need to change to pink to keep track of changes.

Thanks!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Hi and welcome to the board!!!

Code:
Sub ChangeColors()
Dim cl As Range
For Each cl In ActiveSheet.UsedRange
  If cl.Interior.ColorIndex = 6 Then cl.Interior.ColorIndex = 38
Next cl
End Sub

In a regular module

lenze
 
Upvote 0
Hi Lenze,

thanks for the welcome (I am brand new), and for the super-speedy response. Can you give me a bit more information? How/where do I write and execute the script? (as a Macro?). Where do I enter the current color hex or RGB value, and where do I inout the color to which I want the range to change?

Best regards,

Gustavo
Santa Fe, NM
 
Upvote 0
I'm guessing at your hexadecimal numbers, so adjust as needed
Code:
Sub ChangeColors()
Dim cl As Range
For Each cl In ActiveSheet.UsedRange
  If cl.Interior.Color = 65535 Then cl.Interior.Color = 13408767
Next cl
End Sub

It much easier, however to stick with the standard colors and use ColorIndex

The code goes in a regular module. Enter ALT+F11 to open the Visual Basic Editor(VBE) Choose insert module and paste the code in the panel. Enter ALT+Q to exit the VBE. Now enter ALT+F8 to show your macro list. Select the macro and choose run

HTH
lenze
 
Upvote 0
Thanks so much for the macro shortcuts. That all worked. However, my colors are not changing.

I need to change yellow FFCC00 (hex) 2552040 (rgb) to pink FF99CC (hex) 255153204 (rgb).

Maybe not having used "safe" colors is where the problem is? I'd be happy to send you a snippet of this monster spreadsheet I have. Thanks again.

Gustavo
 
Upvote 0
On a blank sheet, color several cells yellow from the pallet. Now run either of my macros. You will need some data in the range. These cells wiil turn Pink.

Did you change the numbers from my example to yous?

I'll try to look again Monday

lenze
 
Upvote 0
Lenze,

Yes, I did change the colors in your script to the ones I am using. I also followed your advice to run it on a new, blank document. I used the Excel 56 color palette described here:

http://support.softartisans.com/kbview_1205.aspx

To change white cells (with data in them) to magenta as follows:

Sub ChangeColors()
Dim cl As Range
For Each cl In ActiveSheet.UsedRange
If cl.Interior.Color = 255255255 Then cl.Interior.Color = 2550255
Next cl
End Sub

It did not work. Let me know if you can find the solution.

Thanks again for your hell (in the meantime I am doing it one-by-one -:))

Gustavo
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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
Back
Top