VBA Code on Conditional Formating

MichaelJ300

Board Regular
Joined
Oct 30, 2013
Messages
133
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I need code that will highlight everything that is "0" in yellow. There will be different rows so I will need to find the last row and highlight everything in columns F through N.

Thank you in advance,

MJHello,

I need code that will highlight everything that is "0" in yellow. There will be different rows so I will need to find the last row and highlight everything in columns F through N.

Thank you in advance,

MJ

222426262728283031
22242626270283031
222426262728283031
2202626272828300
22242626270283031
22242602728283031
222426262728283031
22242626272828031
02426262728283031
222402627280300
222426262728283031



<colgroup><col style="width: 48pt;" span="9" width="64">
<tbody>


</tbody>
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

vikas9385

Board Regular
Joined
Aug 29, 2009
Messages
96
Code:
Sub cond_frmt()
'
' Macro1 Macro
'


'
    Dim lr As Long, r As Long 'declartion


    lr = WorksheetFunction.CountA(ActiveSheet.Range("a:a")) 'function to count data rows


    
    
    Range("F1:N" & lr).FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=0"
    Range("F:N").FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False


End Sub



Hello,

I need code that will highlight everything that is "0" in yellow. There will be different rows so I will need to find the last row and highlight everything in columns F through N.

Thank you in advance,

MJHello,

I need code that will highlight everything that is "0" in yellow. There will be different rows so I will need to find the last row and highlight everything in columns F through N.

Thank you in advance,

MJ

222426262728283031
22242626270283031
222426262728283031
2202626272828300
22242626270283031
22242602728283031
222426262728283031
22242626272828031
02426262728283031
222402627280300
222426262728283031

<tbody>
</tbody>
 

MichaelJ300

Board Regular
Joined
Oct 30, 2013
Messages
133
Office Version
  1. 2016
Platform
  1. Windows
I think it's close to working but it's not. I'm getting this error at the Color = 65535. "Compile error: Assignment to constant not permitted"


' Macro5 Macro
'
Sheets("Priority Ranking Sht").Select
Dim lr As Long, r As Long 'declartion
lr = WorksheetFunction.CountA(ActiveSheet.Range("a:a")) 'function to count data rows
Range("g33:u" & lr).FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=0"
Range("g:u").FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
PatternColorIndex = xlAutomatic
Color = 65535
TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
'
End Sub
 

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,456
Try this:
Code:
[COLOR=#0000ff]Sub[/COLOR] [COLOR=#333333]Macro5 
[/COLOR]
[COLOR=#0000FF]    Dim [/COLOR][COLOR=#333333]lr [/COLOR][COLOR=#0000FF]As Long
[/COLOR][COLOR=#0000ff]    Dim[/COLOR][COLOR=#333333] r[/COLOR][COLOR=#0000FF]  As Long [/COLOR][COLOR=#008000]'declarations go at the top of procedures
[/COLOR]
[COLOR=#333333]    Sheets("Priority Ranking Sht").Select[/COLOR]

[COLOR=#333333]    lr = WorksheetFunction.CountA(ActiveSheet.Range("a:a")) [/COLOR][COLOR=#008000]'function to count data rows[/COLOR][COLOR=#333333][/COLOR]
[COLOR=#333333]    Range("g33:u" & lr).FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _[/COLOR]
[COLOR=#333333]    Formula1:="=0"[/COLOR]
[COLOR=#333333]    Range("g:u").FormatConditions(Selection.FormatConditions.Count).SetFirstPriority[/COLOR]
[COLOR=#0000ff]    With [/COLOR][COLOR=#333333]Selection.FormatConditions(1).Interior[/COLOR]
[COLOR=#333333]        .PatternColorIndex = xlAutomatic[/COLOR]
[COLOR=#333333]        .Color = 65535[/COLOR]
[COLOR=#333333]        .TintAndShade = 0 [/COLOR][COLOR=#008000]  <------ Try putting periods in front of these values[/COLOR]
[COLOR=#0000ff]    End With[/COLOR]
[COLOR=#333333]    Selection.FormatConditions(1).StopIfTrue =[/COLOR][COLOR=#0000ff] False[/COLOR]

[COLOR=#0000ff]End Sub[/COLOR]
 

MichaelJ300

Board Regular
Joined
Oct 30, 2013
Messages
133
Office Version
  1. 2016
Platform
  1. Windows
nope didn't work... Think I'll punt on this one. Thanks everyone!
 

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,456
Not sure exactly what you want but try this:

Code:
[COLOR=#0000ff]Sub[/COLOR] Macro5()

    [COLOR=#0000ff]Dim[/COLOR] lr [COLOR=#0000ff]As Long[/COLOR]
  [COLOR=#0000ff]  Dim [/COLOR]r  [COLOR=#0000ff]As Long[/COLOR][COLOR=#008000] 'declarations go at the top of procedures[/COLOR]

    Sheets("Priority Ranking Sht").Select

    lr = WorksheetFunction.CountA(ActiveSheet.Range("A:A"))[COLOR=#008000] 'function to count data rows[/COLOR]
    Range("G33:U" & lr).FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=0"
    [COLOR=#0000ff]With[/COLOR] Range("G:U")
    .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
       [COLOR=#0000ff] With[/COLOR] .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
[COLOR=#0000ff]        End With[/COLOR]
[COLOR=#0000ff]    End With[/COLOR]
    Selection.FormatConditions(1).StopIfTrue = False
    
[COLOR=#0000ff]End Sub[/COLOR]



Or This

Code:
[COLOR=#0000ff]Sub [/COLOR]Macro5()

   [COLOR=#0000ff] Dim[/COLOR] lr[COLOR=#0000ff] As Long[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] r  [COLOR=#0000ff]As Long[/COLOR] [COLOR=#008000]'declarations go at the top of procedures[/COLOR]

    Sheets("Priority Ranking Sht").Select

    lr = WorksheetFunction.CountA(ActiveSheet.Range("A:A")) [COLOR=#008000]'function to count data rows[/COLOR]
[COLOR=#0000ff]    With[/COLOR] Range("G33:U" & lr)
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=0"
        .FormatConditions(1).StopIfTrue = False
      [COLOR=#0000ff]  With[/COLOR] .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
[COLOR=#0000ff]        End With[/COLOR]
[COLOR=#0000ff]    End With[/COLOR]

[COLOR=#0000ff]End Sub[/COLOR]

One of them should work. Depending on what you have selected...
 

Watch MrExcel Video

Forum statistics

Threads
1,108,810
Messages
5,525,008
Members
409,615
Latest member
papaluigi94

This Week's Hot Topics

Top