Skilled Macro: Find columns by name and highlight them certain colors based on text in another column

[VBAProIWish]

Hello all,

I have a worksheet with 20+ columns. For this macro, I only need to focus on 4 of them. However, none of these columns are ever in a fixed position so the macro would need to find them by name and NOT by column position. Here they are...

1. Vacation Type (will only have a text value of either "Cold" or "Warm")

2. Vacation Started (will always have a date *x/xx/xxxx)

3. Vacation Ended (sometimes it will have a date '*x/xx/xxxx' and sometimes it will NOT have a date and will be truly blank)

4. Number of Days (currently has ALL truly blank cells)

THIS WHOLE MACRO SHOULD NOT BE CASE SENSITIVE ANYWHERE



Here's what I would like the macro to do...

Scenario 1 - for "Cold" values
Find "Cold" text values in the "Vacation Type" column​

"Cold" values WITH a date in the "Vacation Ended" column​

IF there IS a date in the "Vacation Ended" column in the same row, put the number of days difference between the "Vacation Started" column and "Vacation Ended" column in the "Number of Days" column.

The amount of days in the "Number of Days" column will determine whether these cells should be highlighted GREY or RED.

A) IF the number of days difference is 7 days or less, highlight the cells in the "Vacation Ended" column and "Number of Days" column RED.​

OR...

B) IF the number of days difference is MORE than 7 days, highlight the cells in the "Vacation Ended" column and "Number of Days" column GREY.

AGAIN, A & B above are ONLY if the text value in the "Vacation Type" column says "Cold" with some date in the "Vacation Ended" column.

Scenario 2 - for "Cold" values
Find "Cold" text values in the "Vacation Type" column

"Cold" values WITHOUT a date in the "Vacation Ended" column​

​

C) If there is NOT a date in the "Vacation Ended" column in the same row, put a "-" in the "Number of Days" column and highlight the cells in the "Vacation Ended" column and the "Number of Days" column GREY.

AGAIN, C) Above Is ONLY if the text value in the "Vacation Type" column says "Cold" and has NO date in the "Vacation Ended" column.

Scenario 1 - for "Warm" values
Find "Warm" text values in the "Vacation Type" column​

"Warm" values WITH a date in the "Vacation Ended" column​

D) If there IS ANY date in the "Vacation Ended" column in the same row, put the number of days difference in the "Number of Days" column and highlight the cells in the "Vacation Ended" column and the "Number of Days" column GREY.

AGAIN, D) above is ONLY if the text value in the "Vacation Type" column says "Warm" with some date in the "Vacation Ended" column.

Scenario 2 - for "Warm" values
Find "Warm" text values in the "Vacation Type" column

"Warm" values WITHOUT a date in the "Vacation Ended" column​

E) If there is NOT a date in the "Vacation Ended" column in the same row, put a "-" in the "Number of Days" column and highlight the cells in the "Vacation Ended" column and the "Number of Days" RED.

AGAIN, E) Above Is ONLY if the text value in the "Vacation Type" column says "Warm" and has NO date in the "Vacation Ended" column.




Wow, It'll be very interesting to see how all you pros come up with this code. I feel like a kid waiting to open up presents!

Thanks now and thanks later!

 

[Sandeep Warrier]

sandeep.warrier

All I can say is

WWWWoooWWWWW !!!!

PERFECTION TO THE MAX!

I never thought I'd be able to explain what I want in this macro, let alone actually get the code for it.

You are truly a great programmer!

Many many thanks!

You're welcome... and thanks a lot ..
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[VBAProIWish]

Hi

sandeep.warrier

Sorry to bother you again, but I now noticed something

I have other column headers that contain the word "vacation" and the macro won't work if there are columns that also begin with the word "vacation".

Once I remove all columns with the word "Vacation" in any column header (except "Vacation Type") the macro works perfectly as it did before.

Any thoughts,

thanks much,

VBAProIWish

 

[Sandeep Warrier]

Hi

sandeep.warrier

Sorry to bother you again, but I now noticed something

I have other column headers that contain the word "vacation" and the macro won't work if there are columns that also begin with the word "vacation".

Once I remove all columns with the word "Vacation" in any column header (except "Vacation Type") the macro works perfectly as it did before.

Any thoughts,

thanks much,

VBAProIWish

Hello,

I wonder why you face this problem. Cuz the code shud find "Vacation Type" etc... and not just look at vacation. It may create problems if the headers start with "Vacation Type XYZ" etc...

Anyway... try this small change...

In the 1st part of the code... that says

With Selection

.Find..........

End With

Change

LookAt:=xlPart

to

LookAt:=xlWhole

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[VBAProIWish]

sandeep.warrier

That nailed it!

Thanks once again!