MrExcel Publishing
Your One Stop for Excel Tips & Solutions

What am I doing wrong?


Posted by Elizabeth on July 31, 2000 3:51 PM

I'm trying to set up conditional color for specific cells and it's not working, what am I doing wrong? (I do have more than 3 conditions)

Sub colorcells()
Range("A6,A10,A17").Select

For Each Cell In Selection

If Cell.Value = "basescenario" Then

'color for base plan
Cell.Interior.ColorIndex = 3

ElseIf Cell.Value = "scenario1" Then
'color for scenario 1
Cell.Interior.ColorIndex = 6

ElseIf Cell.Value = "scenario2" Then
'color for scenario 2
Cell.Interior.ColorIndex = 39

End If

Next Cell
End Sub


Posted by Celia on August 01, 0100 2:31 AM

Elizabeth
Based on you reply, it sounds like David's suggestion is the answer - I.E. if the cell values in your code are Names as opposed to the actual text in the cells, then the quote marks must be removed.
Celia

Posted by Celia on July 31, 0100 4:55 PM

Elizabeth
Can't see anything wrong with your code. Where is it not working?
Celia

Posted by Elizabeth on July 31, 0100 6:47 PM

Hi Celia,
it selects the range just fine and then does nothing. I get no error messages, nothing.

Would it make a difference if the named cells were defined in a different spreadsheet in the same file?

Thanks for your help,
Elizabeth

Posted by David on July 31, 0100 11:18 PM

I think I understand what is the value of cells a6 a10 and a17 are they actaully the words basescenario or are they some value that basescenario is representing.

try your formula again if i am right without the "" signs such as

Sub colorcells()
Range("A6,A10,A17").Select

For Each cell In Selection

If cell.Value = basescenario Then

'color for base plan
cell.Interior.ColorIndex = 3

ElseIf cell.Value = scenario1 Then
'color for scenario 1
cell.Interior.ColorIndex = 6

ElseIf cell.Value = scenario2 Then
'color for scenario 2
cell.Interior.ColorIndex = 39

End If
Next cell

End Sub