Cell background colour to be highlighted in yellow and font to be in red within a For Each/Next code

[StevieMP]

Hi All,
I've got this bit of code - What I want to add into it is when it is updated with the comment, the background colour is highlighted in yellow and the font is highlighted in red.
Basically, it looks for a match between 2 worksheets and when it finds a match a comment is added to the required cell. The comment I want to be highlighted with a cell background of yellow and font of red.
The code is:

Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, FR As Long

Application.ScreenUpdating = False

Set w1 = Worksheets("TradeWeb")
Set w2 = Worksheets("Log")

For Each c In w1.Range("TradeWebFund", w1.Range("B" & Rows.count).End(xlUp))
FR = 0
On Error Resume Next
FR = Application.Match(c, w2.Columns(5), 0)
On Error GoTo 0
If FR <> 0 Then w2.Range("I" & FR).Value = "SP - Email sent " & Now

Next c

Application.ScreenUpdating = True

 

[BSALV]

in the macro above, i started by saying colors(6) which is normally yellow is now red.
Then excel has 255^3 different colors, but you can reduce them to 56 "colorindexes".
That's fine because my eye isn't that good and doesn't see the difference between RGB(255,0,0) and RGB(254,1,2).

But now comes the funny part :
i thought because of that first line " ActiveWorkbook.Colors(6) = vbRed ", excel 'll not be able to recognize (hundreds of variations) yellow as colorindex 6=yellow, because now it's red,but it does.
But when you apply colorindex 6 it's red.
It's like colorindex has a fixed historical color (done by MS) and at the same time a (sometimes different) custom color.
The first thing is great, reduce 255^3 colors to 56 (historical) colors.
But if you previous said that colorindex 6 is vbred, why didn't raise an error or something else with a yellow RGB-color ?

In your words, 2 palettes : the historical "colorpalette" and the actual/custom colorpalette, it's confusing.

 

[RoryA]

The entire palette is changeable so I can't see why it should return an error?

Usually, the returned colorindex is the closest colour match - though I'm not really sure what the algorithm for that is - but it does seem like that may be based on the default palette, not any custom one. If you assign a Color value that exactly matches the RGB for a Colorindex, then it does appear to reflect the change to the Workbook.Colors value. In other words, if you run something like:

   ActiveWorkbook.Colors(3) = vbYellow
   ActiveWorkbook.Colors(6) = vbRed
   ActiveWorkbook.Colors(27) = vbMagenta
   With Range("B1")
      .Interior.Color = vbYellow
      .Value = .Interior.ColorIndex
   End With
   With Range("B2")
      .Interior.Color = RGB(242, 254, 25)
      .Value = .Interior.ColorIndex
   End With
   With Range("B3")
      .Interior.ColorIndex = 6
      .Value = .Interior.ColorIndex
   End With

B1 will be yellow with the colorindex value 3 (exactly matches the actual palette's new colorindex 3)
B2 will be a different shade of yellow with the colorindex value 6 (closest match to the default palette)
B3 will be red with the colorindex value 6 because that was explicitly set.

 

[BSALV]

Loud and clear, you're right.
But the confusing part is, with a slight difference in RGB-color in the middle of a "colorindex", thus no exact match, you can get another colorindex. Nothing to worry about.