VBA Conditional formatting

[shmidt]

Hello Guys,
Can someone help me with the code. I need to format entire Row "R"


https://www.dropbox.com/s/7og9wi4usadd4fp/ExcelCond.PNG?dl=0


Highlight if any cell contains cnf.

Thank you.

 

[Lobsterboy1]

Click conditional formatting
Click use a formula to determine which cells to format
enter =COUNTIF(R:R,"*cnf*") as your formula
Apply which ever formatting you need
Click ok
In the applies to box enter =$R:$R
click apply.

now if any cell in column R contains cnf it will highlight the entire column R.

Sorry about no pictures I am on my phone.

 

[shmidt]

Click conditional formatting
Click use a formula to determine which cells to format
enter =COUNTIF(R:R,"*cnf*") as your formula
Apply which ever formatting you need
Click ok
In the applies to box enter =$R:$R
click apply.

now if any cell in column R contains cnf it will highlight the entire column R.

Sorry about no pictures I am on my phone.

Thank you . I know how to do this manually, I`m looking for VBA script

Thanks,

 

[Lobsterboy1]

Thank you . I know how to do this manually, I`m looking for VBA script

Thanks,

Sorry. I didn't read you question properly. I will look when I get to work tomorrow

 

[MARK858]

Thank you . I know how to do this manually, I`m looking for VBA script

Thanks,

If you know how to do it manually does the macro recorder not give you the code

 

[Lobsterboy1]

Try this

Sub HIGHLIGHT_cnf()
    With Application
         .Calculation = xlCalculationManual
         .ScreenUpdating = False
    Const TEST_COLUMN As String = "R"
    Dim cell As Range
   ssheetname = ActiveSheet.Name
     
    With Worksheets(ssheetname)
        Range("R:R").Interior.ColorIndex = 0
        For Each cell In Range("R:R")
            If Not IsError(cell.Value) Then
                If cell.Value = "cnf" Then
                    cell.EntireColumn.Interior.ColorIndex = 3
            End If
        End If
       Next
    End With
    .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub

How are you going to make it run? Manually or do you want to check for cell changes in column R.

 

[Worf]

Without the loop:

Sub CondF()
Dim rng As Range, f$
f = "=countif(r:r,""*cnf*"")"
f = "=CONT.SE(R:R;""*cnf*"")"                                   ' choose a formula
Set rng = [r:r]
With rng
    .FormatConditions.Add xlExpression, , f
    .FormatConditions(rng.FormatConditions.count).SetFirstPriority
    .FormatConditions(1).Interior.Color = 45990
    .FormatConditions(1).StopIfTrue = 0
End With
End Sub