CODEWITHSUNDEEP
excelvbaconditional-formatting
Eunito
Eunito

Reputation: 436

Conditional formating based on formula in another sheet

I have 2 Excel 2007 PT-PT sheets: One (sheet1) has multiple columns to be validated. The other (base_valid) has 5 columns to be validated. I'm trying to validate the "regioes" (M2) column using a macro:

Sub Validar_Regioes()
    Dim rg As Range
    Dim cond1 As FormatCondition, cond2 As FormatCondition, cond3 As FormatCondition
    Set rg = Range("M2", Range("M2").End(xlDown))

    'clear any existing conditional formatting
    rg.FormatConditions.Delete

    'define the rule for each conditional format
    Set cond1 = rg.FormatConditions.Add(xlCellValue, xlExpression, "=COUNTIF(base_valid!$B$6:$B$10|M2)>0")

    'define the format applied for each conditional format
    With cond1
    .Interior.Color = vbGreen
    .Font.Color = vbWhite
    End With

End Sub

The idea is to check if the region in M2 column matches any of the values in the base_valid regions range (base_valid!$B$6:$B$10) I keep getting "invalid procedure call or argument" message when I call the macro.

What am I doing wrong?

Upvotes: 3

Views: 744

Answers (1)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

You have to be tricky here. Conditional formatting rules don't allow to add references to ranges on other worksheets, not even manually! An alert will pop up!

enter image description here

The pop up alert says can't use references to other workbooks or worksheets for criterias in conditional formatting

But Excel got another function that can help, called INDIRECT

INDIRECT function

This wonderful function will allow us to reference a range in a different worksheet but as it where on same worksheet. To reference a range, use the reference name as string (as text)

So the formula we will be using as criteria is:

=COUNTIF(INDIRECT("base_valid!$B$6:$B$10");M2)

So your VBA code needs to be fixed like this:

Dim rg As Range
Dim cond1 As FormatCondition, cond2 As FormatCondition, cond3 As FormatCondition
Set rg = Range("M2", Range("M2").End(xlDown))

Set cond1 = rg.FormatConditions.Add(xlExpression, , "=COUNTIF(INDIRECT(""base_valid!$B$6:$B$10"");M2)")

With cond1
    .Interior.Color = vbGreen
    .Font.Color = vbWhite
End With

This worked for me perfectly! After applying macro, I get this:

enter image description here

oh, my data in base_valid worksheet is like this:

enter image description here

Hope you can adapt this to your needs.

UPDATE DECEMBER 2019:

Thanks to @BigBen, another option is using a global-scoped named range, instead of indirect. In this case, you can create a name, let's say, MyValuesList, which references range base_valid!$B$6:$B$10.

Then, the CF rule would be =COUNTIF(MyValuesList;M2) and it would work properly.

So if you use a named range, there is no need of using INDIRECT.

Your code could be:

Set cond1 = rg.FormatConditions.Add(xlExpression, , "=COUNTIF(MyValuesList;M2)")

Upvotes: 4

Related Questions