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

 

[RoryA]

Change the line before Next c to this:

If FR <> 0 Then
   With w2.Range("I" & FR)
      .Value = "SP - Email sent " & Now
      .Interior.Color = vbYellow
      .Font.Color = vbRed
   End With
End If

 

[BSALV]

     If FR <> 0 Then
          With w2.Range("I" & FR)
               .Value = "SP - Email sent " & Now
               .Interior.ColorIndex = 6
               .Font.ColorIndex = 3
          End With
     End If

almost the same as RoryA, color vs colorindex

 

[StevieMP]

     If FR <> 0 Then
          With w2.Range("I" & FR)
               .Value = "SP - Email sent " & Now
               .Interior.ColorIndex = 6
               .Font.ColorIndex = 3
          End With
     End If

almost the same as RoryA, color vs colorindex

Thank you both....really appreciate your help. I like the fact you can use vbYellow/vbRed as well as the numbers 6/3.
Thanks once again

 

[RoryA]

Just be aware that if you use colorindex, you will get whatever colour the user has set in their colour palette at that position, which is not necessarily the colour you chose. That's either a good thing or a bad thing, depending on your objective.

 

[StevieMP]

Thank you. Understand.
Cheers RoryA

 

[BSALV]

you will get whatever colour the user has set in their colour palette at that position

... meaning that you can change for instance ".colorindex =6", normally yellow into blue ?
I thought that was fixed, solid, unchangeble.

VBA ColorIndex Property of Excel Range Shape Chart Background Color

VBA ColorIndex Property of Excel VBA is very useful to set the fill colors, border colors and font colors. Excel VBA ColorIndex returns index values from 1 to 56, -4105 and -4142. You can set the default colors using VBA enumeration number -4105 ( or xlColorIndexAutomatic). We can set VBA...

analysistabs.com

 

[RoryA]

Yes, it can be changed. For example, step through this code and watch the active cell:

Sub AlterColours()
    ActiveCell.Interior.ColorIndex = 6
    ActiveWorkbook.Colors(6) = vbRed
end sub

 

[BSALV]

a late and off topic reaction to @RoryA in #8.
I'm confused about that colorindex and colors(i)
As above, i make 6(=normally yellow) red, then a make lots of colors with RGB(255,x,y) with x and y in the range 5-255
Ask the colorindex of that color and funny excel recognize the visual yellow still as colorindex 6, but that is previous changed to red. In the next line, you fill that cel with that colorindex and now is 6 red.
conclusion and confusion : the colorindex of a RGB-color that is visual yellow remains 6, although 6 is now red
Am i making a mistake somewhere ?

Sub testcolor()
     ActiveWorkbook.Colors(6) = vbRed                           '6 is normally yellow, but now it's red
     For i = 1 To 51                                            'the rows = the G (green) in steps of 5 from 5 to 255
          For j = 1 To 51                                       ' the columns = the B (blue) in steps for 5 from 5 to 255
               With Cells(i, j)
                    .Interior.Color = RGB(255, i * 5, j * 5)    'color a matrix of 51*51 with a corresponding RGB-color with R=255
                    .Value = .Interior.ColorIndex               'write in the cell the colorindex corresponding to that RGB-value
                    .Interior.ColorIndex = .Value               'and now the confusing part, color that cell with that colorindex
               End With
          Next
     Next
End Sub

I tried to add a image, but that was too big, but if you add above code in an empty workbook, a first time without that 3rd line " .Interior.ColorIndex = .Value" in the bottomLeft corner, you 'll have yellow cells.
Run the macro a second time with that 3rd line and the bottomleft corner is red.
Yellow remains colorindex 6, but you can foul the system and make 6 into red ???

 

[RoryA]

There is a disconnect between the two properties. If you set the Colorindex for a cell, and then move it to a workbook with a different colour palette, the cell colour will change to whatever colour is at that index in the new workbook; if you set the Color property, that is fixed regardless of the palette.

I'm not sure what the logic is for returning a colorindex when the cell colour's RGB value is not an exact match for any of the workbook palette colours, but that appears to be where the disconnect is in your example. If you use the exact workbook colours, then the colorindex should adjust appropriately.