VBA to conditionally change background color...

[WiccaChic]

This is going to seem like a dumb and pointless issue, but its a user request from a very impotrant user, so I hope you understand.

I have a an Excel97 worksheet that has about 500,000 cells that have a black background (others have other various color backgrounds) and I want to change just the cells with black backgrounds to have a red background. I figured there must be a simple way to do this using VBA or VBS...but I will admit I am a total total VBA tard!

Please help!

 

[TommyGun]

Hmm....maybe something like this...

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ChangeColors()
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
    
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Selection
        <SPAN style="color:#00007F">If</SPAN> c.Interior.Color = vbBlack <SPAN style="color:#00007F">Then</SPAN> c.Interior.Color = vbRed
    <SPAN style="color:#00007F">Next</SPAN>

    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[al_b_cnu]

Hi,

how about this code:

Sub xxx()
Dim R As Range

For Each R In Sheets("Sheet1").UsedRange
    
    If R.Interior.ColorIndex = 1 Then
        R.Interior.ColorIndex = 3
    End If
Next R

End Sub

To implement:
1) [Alt-F11]
2) Insert|Module
3) Paste above code into the module window.

HTH

Alan

 

[WiccaChic]

Thanks folks! Your posts got me right on track with what I needed to do.