simple but brain dead at the moment

[tf37]

I have a range of cells ("A3:F400") and if they equal "SEE HC" then I wish for that cells color index to be 36.
If range("A3:F400") = "SEE HC" then
cell.select color.index=36
end if
or
Range("A3:F400").Select
If Range = "SEE HC" Then
cell.Select Color.Index = 36
End If
Perhaps I should go back and take a nap for a while...sigh...so simple...
Thanks gang

 

[xenou]

Try this:

Dim r as Range
Dim c as Range
Set r = Range("A3:F400")
For Each c in r
    If c.Value = "SEE HC" Then
    c.Interior.ColorIndex = 36
Next c

Then nap...

Note that you *could* accomplish this with conditional formatting also (without VBA)...

Edit:
Right...
Changed c.ColorIndex to c.Interior.ColorIndex as noted by Paul-H
(also, this is XL2003 code. Untested in XL2007/2010)

 

[Taul]

try one of these
.Interior.ColorIndex = xlNone
.Interior.ColorIndex = 36
.Font.ColorIndex = 1

 

[tf37]

I think the
Dim r as Range
Dim c as Range
Set r = Range("A3:F400")
For Each c in r
If c.Value = "SEE HC" Then
c.ColorIndex = 36
Next c
will work, but doesn't it require a "end if" ?
Not getting it to function at the moment...using Excel 2000 still
And am trying it under a sub routine for the worksheet...of should I just try it in a macro...
The conditional format...looking at that as well...think I'll take that nap

 

[mirabeau]

Try this:

Dim r as Range
Dim c as Range
Set r = Range("A3:F400")
For Each c in r
    If c.Value = "SEE HC" Then
    c.Interior.ColorIndex = 36
Next c

Then nap...
Edit:
Right...
Changed c.ColorIndex to c.Interior.ColorIndex as noted by Paul-H
(also, this is XL2003 code. Untested in XL2007/2010)

I don't really like to disturb your well-earned nap xenou, but maybe you really mean

Dim r As Range
Dim c As Range
Set r = Range("A3:F400")
For Each c In r
    If c.Value = "SEE HC" Then c.Interior.ColorIndex = 36
Next c

or 

Dim r As Range
Dim c As Range
Set r = Range("A3:F400")
For Each c In r
    If c.Value = "SEE HC" Then
        c.Interior.ColorIndex = 36
    End If
Next c

 

[tf37]

Got it, thank you gurus
Nap time for me

 

[xenou]

me too