Conditional formatting - highlighting in VBA

Question

I am trying to make the VBA that highlights the entire row if the row contains a word "New".

The applying range of conditional formatting, I am trying to put maybe A1 till AZ2000

the reason, I also had a problem setting range as whole worksheet.

I never learned VBA so I am getting information from internet

I wrote this much but it is not working, to my knowledge, it should work but i don't know why it is not working, it is frustrating how, I fix one, and another problem comes up...lol

Sub Highlighting()

  'Definining the variables:
  Dim rng As Range
  Dim condition1 As FormatCondition

 'Fixing/Setting the range on which conditional formatting is to be desired
  Set rng = ("A1, AZ2000")

  'To delete/clear any existing conditional formatting from the range
   ws.FormatConditions.delete

  'This is where I get Syntax error, it says "New" needs list separator
  Set condition1 = ws.FormatConditions.Add(xlConditionValueFormula, xlGreater, "=FIND(""New"",$AF1)>0)")

  'Defining and setting the format to be applied for each condition
   With condition1
    .EntireRow.Interior.ColorIndex = 10498160
   End With

End Sub

this is how i want it set up in VBA

I believe you may need `xlExpression` instead of `xlGreater` — cybernetic.nomad, Oct 28 '19 at 21:37
@cybernetic.nomad That is the answer. Make an official answer so this can be closed out. https://learn.microsoft.com/en-us/office/vba/api/excel.formatconditions.add — HackSlash, Oct 28 '19 at 22:48
Hi thank you for the advice, but i also had an error message at "Set rng = ("A1, AZ2000") it says type mismatch, is it because i have set the wrong range? — wonkyu lee, Oct 28 '19 at 23:11

user 88 91 · Answer 1 · 2019-10-29T15:45:57.587

This should work.

I don't know which workbook you apply this to so I given you some options, Remove the ' before set wb or sheetID depending on which ones you're going with (and add it to the others). If you're interested on some more common tips and tricks for coding with vba check out this answer.

Sub Highlighting()

'Definining the variables:
Dim rng As String
Dim condition1 As FormatCondition
Dim wb As Workbook
Dim sheetID As String

'Define workbook to run code against, depending on which fits you comment out/in here:
Set wb = ThisWorkbook ' the excel workbook that contains the vba code
'set wb = Workbooks("nameOfExcelWorkbook.xlsx") ' excel workbook that you've defined (must be an open workbook with the name.file ending inside citation marks)
'set wb = ActiveWorkbook ' the currently selected excel workbook (not recommended)
'Set wb = Workbooks.Open("Filename as string.fileEnding")

'Define what sheet to use
sheetID = ActiveSheet.Name 'currently selected worksheet name (not recommended)
'sheetID = "nameOfSheet"
'sheetID = 1 ' indexed version of above

'Fixing/Setting the range on which conditional formatting is to be desired
rng = "A1:AZ2000"


With wb.Sheets(sheetID).Range(rng)

    'To delete/clear any existing conditional formatting from the range
    .FormatConditions.Delete

    'Apply conditional formating
    Set condition1 = .FormatConditions.Add(Type:=xlExpression, Formula1:="=FIND(""New"",$AF1)>0")

End With
'Defining and setting the format to be applied for each condition
With condition1
    .Interior.Color = RGB(112, 48, 160)
End With

End Sub

Conditional formatting - highlighting in VBA

1 Answers1