Conditional Formating with VLookup and Holidays for different Countries

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,216
Office Version
  1. 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:

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
You can use this formula in Conditional Formatting...

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

M.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top