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

 

[davidhall]

When I run the macro, all it does is fills the blank cells gray and keeps any unique and duplicate data clear filled. How do I change the colors so that the unique data is blue and the duplicate data (other than the blank cells) turns green?

 

[Marcelo Branco]

When I run the macro, all it does is fills the blank cells gray and keeps any unique and duplicate data clear filled. How do I change the colors so that the unique data is blue and the duplicate data (other than the blank cells) turns green?

I run the macro and it worked perfect. I dont know why it is not working for you...

Just in case, try it on a new and empty workbook

M.

 

[davidhall]

It's still doing the same thing. The only color that is showing is gray.

 

[Marcelo Branco]

David,

Just in case (again)

Insert this line immediately after Range("B1:B184").Select

Selection.FormatConditions.Delete

M.

 

[davidhall]

It says "Invalid procedure call or argument"

 

[Marcelo Branco]

David,

Copy exactly the macro below and paste it in a new workbook. I run it several times with no problems. I hope you have a sheet named Sheet2...

By the way, what Excel version do you have?

Sub Macro1()
'
' Macro1 Macro
'
'
    Sheets("Sheet2").Activate
    
    Range("B1:B184").Select
    
    Selection.FormatConditions.Delete
    
    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

M.

 

[davidhall]

OK. This is what I'm doing step by step.

I have opened up a new spreadsheet.
I copied and pasted the items from another spreadsheet which contains the data that will be places in column B.

The items I pasted are only the items that I need in column B and I pasted them on Sheet2.

Right click on Sheet2 and clicked on View Code.

Copied and pasted the VBA you wrote.

Saved the document as a macro-enabled workbook.

Clicked on the developer tab, then macros.

Click on the macro name which is titled Sheet2.Macro1
then clicked on Run.

After I click Run, and display message pops up saying Invalid procedure call or argument

I click on the Ok option and when I view my data, only the blank cells are gray.

I'm not too sure what I'm doing incorrectly.

 

[Marcelo Branco]

David,

I think you have to insert the macro in a standard module. Try this
In Excel
Alt+F11 to open the VBEditor
On the MenuBar click on Insert and pick Module
A new Module will be inserted
On the right panel paste the macro
Back to Excel and run the macro

M.

 

[texasalynn]

instead of right click on sheet name, press Alt + F11 that puts you into vb editor. Then click insert --> Module, now paste your code into that window.

 

[davidhall]

Ok I have done that. Now a message comes up stating

Run-time error '5':

Invalid procedure call or argument


Then it gives me the option to end or debug. When I selece debug, the information below is hightlighted in yellow



Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($B$1:$B$184;B1)>1"