Conditional Formatting

[MR3]

i am trying to add a line(s) of code to my existing VBA script that will select the entire worksheet and change the fill color of any specific cell to #C0C0C0 given that its current fill contents are that of #993366

 

[Rick Rothstein]

Assuming that 993366 number is a Hex value (like the C0C0C0 is), then give this code a try...

Sub ChangeFillColorFromHex993366ToHexC0C0C0()
  Application.FindFormat.Interior.Color = &H993366
  Application.ReplaceFormat.Interior.Color = &HC0C0C0
  Cells.Replace "", "", SearchFormat:=True, ReplaceFormat:=True
End Sub

 

[MR3]

Here is my code:

Sub BMW()

Range("A1").Value = "Name"
Range("B1").value = "Address"
Range("C1").value = "Car"
Rows(1).Font.Bold=True

End Sub

I would like to select all cells that are populated in the entire current worksheet and replace them with a different color
as stated above. The cell being populated meaning it doesnt have to contain a character if it is filled RED (993366) then change
it to grey (C0C0C0)

 

[Rick Rothstein]

Clarification please -- I may have another question or two for you depending on your answer to the following...

Given your subject, I may have misunderstood your original question. That #994466 color... is it produced by Conditional Formatting or did you manually, or via code, change the interior color of the cells in question?

 

[MR3]

so i have a preexisting excel spreadsheet that came from an export of a database of current auto clients. in this preexisting sheet some cells are already formatted or filled with RED in the cell.

So when i run my macro on the entire worksheet i want to change the RED cells to Grey. In otherwords I would like it to grab the range of columns and rows that are occupied with or without data and determine if the current fill is Red and if it is red then, based upon that condition I would like it to conditionally format those red cells back to grey.

 

[Rick Rothstein]

... in this preexisting sheet some cells are already formatted or filled with RED in the cell....

I assume you mean by that that those cells are not conditionally formatted. If that is the case, then the code I posted should be able to be placed anywhere inside your existing code without interference. Can you post the code you had trouble with (where you tried to intergrate them) so I can test what you did here?

 

[MR3]

when i take out the first and last line of your code I am left with just the middle three lines of code....it gives an error



i think the error is on the color referencing

 

[Rick Rothstein]

I presume by the first and last line of my code, you meant the Sub and End Sub statements. The code I posted was tested before I posted it and it worked fine on my XL2003 worksheet. Tell us which version of Excel you are using and please post the code you are trying to use... it is possible the syntax error was introduced via your editing... if you don't show us the code that is giving you trouble, there is no way we can guess at what may be wrong. Also, what line is highlighted, if any, when you dismiss the error message.

 

[MR3]

I am not sure why the code was error-ing out before, but now it seems to be good, however the colors don't seem to be changing in the cells.

So I guess we could go with another approach. To have the code select the whole sheet and then make sure the background fill of every cell in the entire worksheet is just white.

 

[Rick Rothstein]

I am not sure why the code was error-ing out before, but now it seems to be good, however the colors don't seem to be changing in the cells.

So I guess we could go with another approach. To have the code select the whole sheet and then make sure the background fill of every cell in the entire worksheet is just white.

If you mean you plan to fill each cell's interior with the actual color white, I don't think you will like the result... you will lose your grid lines. Are you sure the color in the cells is HEX 993366? If that is not a Hex number, then remove the &H from the following line of code...

Application.FindFormat.Interior.Color = &H993366