Fill color in Excel Cell

SiliconFuRy · October 25th, 2004, 06:25 AM

Ah, i understand now.

Sort of works, but only on the first cell (it highlights it because its a column header that doesnt contain the string "recovery")

the loop seems to bail out after the first cell... heres the complete code so far... any thoughts?

Sub HighlightCVolume()
  Dim oCell As Excel.Range
  Dim oSheet As Excel.Worksheet

  Set oSheet = ActiveSheet

  For Each oCell In oSheet.Range("F:F").SpecialCells(xlCellTypeConstan ts)
    If InStr(1, UCase$(oCell.Value), "REC", vbTextCompare) = 0 Then
    With oCell.Interior
        .ColorIndex = 4
        .Pattern = xlSolid
      End With
    End If
  Next
End Sub

gcianfanelli · October 25th, 2004, 06:34 AM

Sorry - your code works fine for me. I gave it a go with a worksheet whose cells in F1 thru F5 were populated

RECOVERY
RECOVERY OS
WIN2K_REC
NA
PRODUCTION

It highlighted the last two cells (NA and PRODUCTION). Isn't that what you wanted??

SiliconFuRy · October 25th, 2004, 06:36 AM

Ah, i forgot, all the items in that columns are results from a Vlookup... is it possible to check these values (returned from the vlookup)?

gcianfanelli · October 25th, 2004, 07:04 AM

Maybe do something like

Code:

Sub HighlightCVolume()
  Dim oCell As Excel.Range
  Dim oSheet As Excel.Worksheet

  Set oSheet = ActiveSheet

  For Each oCell In oSheet.Range("F:F").SpecialCells(xlCellTypeFormulas)
    On Error Resume Next
    If InStr(1, UCase$(oCell.Value), "REC", vbTextCompare) = 0 Then
      With oCell.Interior
        .ColorIndex = 4
        .Pattern = xlSolid
      End With
    End If
  Next
End Sub

A good starting point is to read the online help for the functions you're using. Click on SpecialCells and hit F1

SiliconFuRy · October 25th, 2004, 07:11 AM

Ah, excellent, that works fine. Thanks.

I've been trying to get info from the MSDN website (worked a treat when doing development in C#), but I'm finding it a right bitch finding info in the same way for office development (we're using office 2000 here).

Also forgot about the "hitting F1 while cursor is on a keyword" thing.

Thanks again, this has shaved a few days off development for this report :D