change text color based on background color

I have a report that lists information about different phases of several projects. When others make changes to the sheet, they change the font color of those changes to red. Then after our weekly meeting I go through and change all the red text to black or white (depending on the background color of the cell).
I would like to have a macro that will do this for me as it is very time consuming.

So I'm looking for code that will do the following:
for the cells in columns N,M,Q,R and T,
if the background color is RGB(208, 192, 98), then fontcolor is Black
if the background color is RGB(255, 0, 0), then fontcolor is white,
etc.

I tried to patch together some code (as follows) but it wasn't working.

Sub OldChanges()
Dim Row As Integer
Dim Back As Boolean

  
  For Row = 5 To 200
    

    Back = ActiveSheet.Cells(Row, N).Interior.Color
  
    


    If Back = RGB(208, 192, 98) Then
    ActiveSheet.Cells(Row, N).Font.ColorIndex = 1

Else
ActiveSheet.Cells(Row, N).Font.ColorIndex = 2
End If

Next Row
End Sub

Thanks for your help!
JJH

Hi

I think it has to do with the RGB setting a colorindex that is the nearest to it in the palette you are using, so the resulting color may not be the same as the one that you think you have nominated.

This will have to be adapted for your loop but try something like

Range("A1").Interior.Color = RGB(208, 192, 98)
  holder = Range("A1").Interior.ColorIndex
  Range("A1").Interior.ColorIndex = xlNone
  If ActiveCell.Interior.ColorIndex = holder Then
    ActiveCell.Font.ColorIndex = 1
  Else
    ActiveCell.Font.ColorIndex = 2
  End If

What I've done is select a cell that has no color, put in the color you have nominated, determined which colorindex that results, then return it to no color (simplistically speaking). Then you test against that resulting colorindex.

HTH

rylo

Thank you so much. That is just what I need. Thank you especially for your explicet step by step instructions.

One problem is that I am working in a report that my boss created, and his background colors are not in the Colorindex list.
I think it would work to see if the background color maches a refference cell perhaps something like this:

Sub OldChanges1()
  Dim rCurrent As Range
  Dim rMissed As Range
  Dim rCompleted As Range
  
  rCurrent = Range("'Reference (DO NOT DELETE)'!A1")
  rMissed = Range("'Reference (DO NOT DELETE)'!A3")
  rCompleted = Range("'Reference (DO NOT DELETE)'!A2")

 'Cell Interior Colors
  Const ciBrown As Long = rCurrent.Interior.ColorIndex
  Const ciBlue As Long = rCompleted.Interior.ColorIndex
  Const ciPink As Long = rMissed.Interior.ColorIndex
  Const ciWhite As Long = 2
  
 'Font Colors
  Const fcBlack As Long = 1
  Const fcWhite As Long = 2
  
  Dim C As Long
  Dim colArea As Range
  Dim LastCell As Range
  Dim R As Long
  Dim Rng As Range
  
    Set Rng = Range("H:H,M:N,P:Q,S:T,V:W,Y:Y,AA:AB,AD:AE,AG:AG")
    
    For Each colArea In Rng.Areas
      Set LastCell = colArea.Find("*", , xlFormulas, xlWhole, xlByRows, xlPrevious, False)
        If Not LastCell Is Nothing Then
          C = LastCell.Column
          For R = 5 To LastCell.Row
            Select Case Cells(R, C).Interior.ColorIndex
              Case ciBlue
                Cells(R, C).Font.ColorIndex = fcWhite
              Case ciWhite
                Cells(R, C).Font.ColorIndex = fcBlack
              Case ciBrown
                Cells(R, C).Font.ColorIndex = fcBlack
              Case ciPink
                Cells(R, C).Font.ColorIndex = fcBlack
            End Select
          Next R
        End If
    Next colArea
    
 End Sub

I tried that, but it won't set that colorindex as a constant. Any thoughts?

JJH