Changing cells colour programatically?

[Simon Lloyd]

Hello all,

I am trying to change all cells on a work sheet or a named range to clear (or white) using VBA, i want to be able to change a cells colour back to its normal state.

Can anyone help?..........here's what i have so far you can run it but it does nothing!

Simon

Sub colourchange()
Dim mycell
Dim rng as Range
'For Each mycell In rng("colourrange")
With Worksheets("sheet1")
Set rng = Range("A1:C7")
For Each mycell In rng
If rng.Interior.ColorIndex = 38 Then
rng.Interior.ColorIndex.Format.Clear
End If

Next mycell
End With
End Sub

[davidm]

try:

Sub colourchange()
Dim mycell
Dim rng As Range

Set rng = Range("A1:C7")
For Each mycell In rng
If rng.Interior.Colorindex =38 Then
'rng.Interior.ColorIndex.Format.Clear
rng.Interior.ColorIndex = xlNone
End If

Next mycell

End Sub

[Simon Lloyd]

Well thanks for your reply, i tried your modification ran it on the sheet but like my code seemed to do nothing, am i doing something wrong?

Simon

[Leith Ross]

Hello Simon,

Try this code...

Sub colourchange()

Dim MyCell
Dim Rng As Range

Set Rng = Worksheets("Sheet1").Range("A1:C7")

For Each MyCell In Rng
MyCell = Rng.Interior.ColorIndex
If MyCell = 38 Then
Rng.Interior.ColorIndex = xlNone
End If
Next

End Sub

Sincerely,
Leith Ross

[swatsp0p]

May I ask if you are sure the affected cells are truly color 38? Your code will only change to 'xlnone' those that are currently color38 (which is the color "Rose").

Just a thought.

Bruce

[Simon Lloyd]

I tried the code modification but i had tried something similar and it still appears to do nothing, i recorded a macro selecting the colour so that i was dead sure the colour index was right........it's driving me mad!

All i want to do is find all the instances in a range of a set colour and turn them back to xl's normal state, it should have been simple but im making no headway, i'm using xl2003.

Simon

[swatsp0p]

Making sure you have a backup of your file (just in case!!), alter your code to this to see what happens:

instead of

Rng.Interior.ColorIndex = xlNone

try

Rng.Interior.ColorIndex = 0

Does this change anything?

Also verify the Set Rng = Worksheets("Sheetxx").Range("xx:xx") is pointed to the correct sheet name and cell range

Good Luck!!!

Bruce

[Simon Lloyd]

Cheers for the suggestion Bruce, tried that but to no avail, the code still appears to do nothing. I have tried it in a test book with different colours but still the same problem!

It has to be something simple im missing.....but its a case of i cant see the wood for the trees

Simon

[Simon Lloyd]

Hi all, I have messed around with the code as below and when i run it i dont even get the message box up even though all the other cells in my test book are blank. Can anyone shed any light on this?

Simon

Sub colourchange()

Dim MyCell
Dim Rng As Range
With ThisWorkbook.Worksheets("Sheet1")
Set Rng = Worksheets("Sheet1").Range("A1:C7")

For Each MyCell In Rng
MyCell = Rng.Interior.ColorIndex
If MyCell = 38 Then
'Rng.Interior.ColorIndex = 0
MsgBox "Hi"
ElseIf MyCell = 0 Then
MsgBox "Bye"
End If
Next
End With
End Sub

[Simon Lloyd]

After much swapping and changing and plenty of hair fall out i managed to tame the beast!

Code posted below!

Simon

Sub colour1()
Dim Rng As Range
With ThisWorkbook.Worksheets("Sheet1")
Set Rng = Worksheets("Sheet1").Range("A1:H652")
For Each MyCell In Rng
If MyCell.Interior.ColorIndex = 4 Then
MyCell.Interior.ColorIndex = 38
ElseIf MyCell.Interior.ColorIndex = 3 Then
MyCell.Interior.ColorIndex = 6
End If
Next
End With
End Sub