Conditional Formating with VLookup and Holidays for different Countries

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,123
Office Version
2016
Hi guys,

I got in my workbook two named ranges for two countries and there holidays.

A conditional formating I was able to create in my workbook but I would like to have it more dynamic and therefore within vba.

If range("B2"). value = "Germany" I like to use the named range "Holidays_Germany" to conditional format the dates in wksDates.. beginning at range "B5".
If it is "Austria" then use "Holidays_Austria" as the vlookup.

In the normal conditional formating I got it like this .."=vlookup(A5,Holidays_Germany,1,0)

It works fine but how can you achieve that in vba? To have a vlookup is not the issue but combined with a conditional formating I don't know.

Has someone maybe time to help me with this issue?

Greatly appreciated.
 
Last edited:

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
You can use this formula in Conditional Formatting...

=ISNUMBER(MATCH(A5,IF($B$2="Austria",Holidays_Austria,Holidays_Germany),0))

M.
 

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,123
Office Version
2016
Hi,
I will try that too thanks.
But as it is a larger project with vba I would love to have it working within vba.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
To automate in VBA you should use the Worksheet_Change event checking if Target's address is equal to "$B$2".
If so, your code should make a loop, checking if each cell in the range to be formatted is found in the range whose name is contained in B2 (Target) and format accordingly.

M.
 

Watch MrExcel Video

Forum statistics

Threads
1,098,857
Messages
5,465,102
Members
406,412
Latest member
superjoejoe

This Week's Hot Topics

Top