Highlight cells in column if Column name does not include ..

[gripper]

I am struggling to get this code to verify. I am hitting a syntax issue on the line "If UCase(c.Value) Not Like "*STACKER*" Then"

I want the code to look into the column headers and if a partial string match occurs then go down the column and highlight if it meets the criteria

I want it to do this for all matches except if the partial string has the word "Stacker" in the column name.

I cannot figure out the issue.

Thank you in advance for the assistance

Sub HighlightLongShort()
  Dim c As Range

  ' Loop through each cell in the first row of the active sheet
  For Each c In ActiveSheet.Range("1:1").Cells
    ' Check if the column header does not contain "Stacker" (not case sensitive)
    If UCase(c.Value) Not Like "*STACKER*" Then
      ' Check if the column header contains "Long" or "Short" (not case sensitive)
      If UCase(c.Value) Like "*LONG*" Or UCase(c.Value) Like "*SHORT*" Then
        ' Loop through each cell in the column
        For Each cell In c.EntireColumn.Cells
          ' Check if the cell value is not equal to 0
          If cell.Value <> 0 Then
            ' Highlight the cell with the desired RGB color
            cell.Interior.Color = RGB(255, 255, 0)
          End If
        Next cell
      End If
    End

 

[breynolds0431]

Try changing the "Not Like" line to If Not UCase(c.Value) Like "*STACKER*" Then

 

[gripper]

I will give that a try. Thank you.

 

[jdellasala]

I am struggling to get this code to verify. I am hitting a syntax issue on the line "If UCase(c.Value) Not Like "*STACKER*" Then"

I want the code to look into the column headers and if a partial string match occurs then go down the column and highlight if it meets the criteria

I want it to do this for all matches except if the partial string has the word "Stacker" in the column name.

I cannot figure out the issue.

Thank you in advance for the assistance

Sub HighlightLongShort()
  Dim c As Range

  ' Loop through each cell in the first row of the active sheet
  For Each c In ActiveSheet.Range("1:1").Cells
    ' Check if the column header does not contain "Stacker" (not case sensitive)
    If UCase(c.Value) Not Like "*STACKER*" Then
      ' Check if the column header contains "Long" or "Short" (not case sensitive)
      If UCase(c.Value) Like "*LONG*" Or UCase(c.Value) Like "*SHORT*" Then
        ' Loop through each cell in the column
        For Each cell In c.EntireColumn.Cells
          ' Check if the cell value is not equal to 0
          If cell.Value <> 0 Then
            ' Highlight the cell with the desired RGB color
            cell.Interior.Color = RGB(255, 255, 0)
          End If
        Next cell
      End If
    End

You do realize this could probably be done using Conditional Formatting regardless of what version of Excel you're using. It would be helpful if you updated your profile and indicated what OS and Excel Version you're using. Although it's not relevant here (yet?), it's also considerate to use XL2BB when posting data, but that's for another day!