Conditional Formatting VBA Help

[davidhall]

I need help with some conditional formatting with VBA.

I've been reviewing several post similar to what I need but don't know how to put the code together.


This is what I need.

I need the cells from rows B1 to B184 to have a clear fill color if they are blank.

Then I need any duplicate values in the same range of cells B1 to B184 to change to a green fill color.

Then I need any unique values in the same range of cells B1 to B184 to change to a light blue fill color.

Please help

 

[texasalynn]

You don't need VBA for that since you really only have two colors
create the conditional format with formula is in the condition1 drop down
=COUNTIF($B$1:$B$184,B1)>2
add another condition
=COUNTIF($B$1:$B$184,B1)>=1

 

[Marcelo Branco]

Hi,

This i get with the macro recorder

Try it in a test-workbook

Sub Macro1()
'
' Macro1 Macro
'
'
    Range("B1:B184").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=B1="""""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -4.99893185216834E-02
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTIF($B$1:$B$184;B1)>1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTIF($B$1:$B$184;B1)=1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.799981688894314
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

HTH

M.

 

[davidhall]

I need it to be automated when certain data is copied and pasted in the worksheet so yeah VB would be best since making conditional formatting changes when recording a macro doesn't seem to work.

 

[texasalynn]

I need it to be automated when certain data is copied and pasted in the worksheet so yeah VB would be best since making conditional formatting changes when recording a macro doesn't seem to work.

could you explain that a little more???

 

[Marcelo Branco]

I need it to be automated when certain data is copied and pasted in the worksheet so yeah VB would be best since making conditional formatting changes when recording a macro doesn't seem to work.

You can use the macro i've showed, but also is possible to use formulas and copy + paste only values doesnt erase your CF.

M.

 

[davidhall]

Sorry, my reply posted before I was able to see your comment.

Thanks for the VBA. I need to insert this in an existing macro. So I copy and paste it in the VBA for the macro.

If this code needs to reference data on a certain worksheet, where do I put that refence in the VBA you wrote?

 

[davidhall]

Also,

I have date on another worksheet that references data through a vlookup function. Whenever I copy and paste data in the original worksheet, the vlookup function range that was initially designated changes to #REF. Why is that?

 

[Marcelo Branco]

EDIT

I mean Paste Special... Values

M.

 

[Marcelo Branco]

Sorry, my reply posted before I was able to see your comment.

Thanks for the VBA. I need to insert this in an existing macro. So I copy and paste it in the VBA for the macro.

If this code needs to reference data on a certain worksheet, where do I put that refence in the VBA you wrote?

If you want to refer to Sheet2, for example, insert this line as the first one of the macro

Sheets("Sheet2").Activate

M.