Excel VBA Change text colour depending on another cell's content

**jell** · 02-08-2010, 04:50 PM

I have absolutely no experience in VBA;

I am using Excel 2002 and have a spreadsheet comprising columns A – BI and rows 1-900

Entries in column O can be C, N, RC, RN

I have been trying to write a code so that –

If Columns O = C and J is a date in the future, then Column C will automatically be populated with “CW” and the whole of the row will be in dark green text

If Columns O = “C” and J is today or a date in the past, then Column C will automatically be populated with “C” and the whole of the row will be in red text

If Columns O = “N” and P is blank, then Column C will automatically be populated with “W” and the whole of the row will be in dark blue text

If Columns O = “N” and P has a date in it, then Column C will automatically be populated with “E” and the whole of the row will be in black text

If Columns O = “RC” or “RN” and L is blank, then Column C will automatically be populated with “W” and the whole of the row will be in dark blue text

If Columns O = “RC” and L has a date in it, then Column C will automatically be populated with “C” and the whole of the row will be in red text

If Columns O = “RN” and L has a date in it, then Column C will automatically be populated with “E” and the whole of the row will be in black text

Any other entries should default to black text

All of the above to be non-case sensitive.

I’ve managed, almost, to get this to work using an IFAND function and conditional formatting but would rather use VBA because this would prevent any other users from meddling with the functionality.

Lastly can anyone suggest a good site for gaining an understanding of how to write VBA procedures, one that will explain things as if I am a simpleton and use words of not more than 2 syllables

Appreciate any suggestions

**Leith Ross** · 02-08-2010, 08:20 PM

Hello Jell,

This macro will format the row text based on what is in cell "O". Change the worksheet name to what you are using. I was not sure if the last letter after the "B" was "L" or "I" because of the font. Change that if it it isn't right before you run the macro.

Sub FormatText()

  Dim ColC As String
  Dim R As Long
  Dim Rng As Range
  Dim RngEnd As Range
  Dim TextColor As Long
  Dim Wks As Worksheet
  
    Set Wks = Worksheets("Sheet1")
    
    Set Rng = Wks.Range("A1:BI1")
    Set RngEnd = Wks.Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False)
    Set Rng = Rng.Resize(RowSize:=RngEnd.Row - Rng.Row + 1)
     
      For R = 1 To Rng.Rows.Count
        Select Case LCase(Rng.Cells(R, "C"))
          Case Is = "c"
            If Int(Rng.Cells(R, "J")) > Int(Now()) Then
               ColC = "CW"
               TextColor = 10 'Dark Green
            End If
            If Int(Rng.Cells(R, "J")) <= Int(Now()) Then
               ColC = "C"
               TextColor = 3 'Red
            End If
          Case Is = "n"
            If IsEmpty(Rng.Cells(R, "P")) Then
               ColC = "W"
               TextColor = 11 'Dark Blue
            End If
            If IsDate(Rng.Cells(R, "P")) Then
               ColC = "E"
               TextColor = 1 'Black
            End If
          Case Is = "rc"
            If IsDate(Rng.Cells(R, "L")) Then
               ColC = "C"
               TextColor = 3 'Red
            End If
          Case Is = "rn"
            If IsDate(Rng.Cells(R, "L")) Then
               ColC = "E"
               TextColor = 1 'Black
             End If
          Case Is = "rc", "rn"
            If IsEmpty(Rng.Cells(R, "L")) Then
               ColC = "W"
               TextColor = 11 'Dark Blue
            End If
          Case Else
            TextColor = 1 'Black
        End Select
        Rng.Rows(R).EnitreRow.Font.ColorIndex = TextColor
      Next R
      
End Sub

**jell** · 02-12-2010, 05:29 PM

Leith, Thanks, I haven't had a chance to try this on Excel2002, but I have on 2003, unfortunately it doesn't work. I noticed a spelling mistake on your last line but correcting it has made no difference. The macro runs but doesn't update column c

Any suggestions, Thanks, Jell

**excelxx** · 02-15-2010, 11:05 AM

Hi Jell

I think you just need to correct this bit of the code

      For R = 1 To Rng.Rows.Count
        Select Case LCase(Rng.Cells(R, "C"))
          Case Is = "c"

to

      For R = 1 To Rng.Rows.Count
        Select Case LCase(Rng.Cells(R, "O"))
          Case Is = "c"

so that column O is evaluated

also to update Column C you need to add the line

        End Select
        Rng.Rows(R).EntireRow.Font.ColorIndex = TextColor
'you need to add this line of code below
        Rng.Cells(R, "C").Value = ColC
      Next R