Change this code so it appends text to a cell that is highlighted.

I found this code that identifies a cell that is filled with background color and then highlights the column header it is in. How can i change this so it appends text to the front of the cell that is shaded instead. So if the cell is yellow and says "Automotive" in it, after running the script it would say something like
"**CHANGED** - Automotive"

Sub TagColumns()
  Dim headers As Range, body As Range, col As Long, found As Boolean

  ' define the columns for the headers and body
  Set headers = ActiveSheet.UsedRange.Rows(1).Columns
  Set body = ActiveSheet.UsedRange.Offset(1).Columns

  ' iterate each column
  For col = 1 To headers.Count

    ' search for any color in the column of the body
    found = VBA.IsNull(body(col).DisplayFormat.Interior.ColorIndex)

    ' set the header to red if found, green otherwise
    headers(col).Interior.Color = IIf(found, vbRed, vbGreen)
  Next

End Sub

Originally Posted by Richard Buttrey

Hi,

Perhaps the following immediately before the loop's 'Next' instruction

If headers(col).Interior.Color = vbRed Then headers(col) = "**CHANGED** - " & headers(col)

Thanks but this is changing the column header. To be clearer I need to find any highlighted cell on the sheet and then change that specific cell and not the headers. I included this code b/c it was the only thing i could find that was close to what I was looking for.

Hello BeaglesBuddy,

If I've understood your request correctly then the following may help:-

Sub Test()

      Dim c As Range

For Each c In Sheet1.UsedRange.Offset(1) '---->Change sheet name to suit.
      If c.Interior.ColorIndex <> xlNone Then
      c.Value = "**CHANGED**" & "-" & " " & c.Value
      c.Interior.ColorIndex = xlNone
      End If
Next c

End Sub

The code will also clear the cell colour so that it is not over written with each subsequent running of the code.
The code also assumes that row1 has headings and data starts in row2.

I hope that this helps.

Cheerio,
vcoolio.

Originally Posted by vcoolio

Hello BeaglesBuddy,

If I've understood your request correctly then the following may help:-

Sub Test()

      Dim c As Range

For Each c In Sheet1.UsedRange.Offset(1) '---->Change sheet name to suit.
      If c.Interior.ColorIndex <> xlNone Then
      c.Value = "**CHANGED**" & "-" & " " & c.Value
      c.Interior.ColorIndex = xlNone
      End If
Next c

End Sub

The code will also clear the cell colour so that it is not over written with each subsequent running of the code.
The code also assumes that row1 has headings and data starts in row2.

I hope that this helps.

Cheerio,
vcoolio.

Perfect Thanks!