VBA Code on Conditional Formating

[MichaelJ300]

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

22	24	26	26	27	28	28	30	31
22	24	26	26	27	0	28	30	31
22	24	26	26	27	28	28	30	31
22	0	26	26	27	28	28	30	0
22	24	26	26	27	0	28	30	31
22	24	26	0	27	28	28	30	31
22	24	26	26	27	28	28	30	31
22	24	26	26	27	28	28	0	31
0	24	26	26	27	28	28	30	31
22	24	0	26	27	28	0	30	0
22	24	26	26	27	28	28	30	31

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


</tbody>

 

[vikas9385]

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

22	24	26	26	27	28	28	30	31
22	24	26	26	27	0	28	30	31
22	24	26	26	27	28	28	30	31
22	0	26	26	27	28	28	30	0
22	24	26	26	27	0	28	30	31
22	24	26	0	27	28	28	30	31
22	24	26	26	27	28	28	30	31
22	24	26	26	27	28	28	0	31
0	24	26	26	27	28	28	30	31
22	24	0	26	27	28	0	30	0
22	24	26	26	27	28	28	30	31

<tbody>
</tbody>

 

[MichaelJ300]

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]

Try this:

[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]

nope didn't work... Think I'll punt on this one. Thanks everyone!

 

[mrmmickle1]

Not sure exactly what you want but try this:

[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

[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...