VBA to cell color formatting based on column header

[Prince27]

Hello Experts,

I have a large data set with around 150 columns. I want to change the cell color in a given column based on the criteria.

Example of column Name : Plan A, Plan B, Plan C
1. If "Plan A" column has a cell value except 'Hard' or 'Soft', then the rest of the cells should be changed to yellow color.
2. If "Plan A" columns has a cell value except 'Hard' or 'Soft', however columns "Plan B" or "Plan C" says 'NA', no need to change the color.

For the above scenarios I have written VB using If then else using the column reference which is hard coded, and its working as I expected, however if there is interchange or reorder in the columns, then my current code is not working.

I'm looking for a Dynamic code if my above columns (Plan A, Plan B, Plan C etc) can be anywhere in my worksheet of around 150 columns, the above conditions should meet and the cell colors accordingly. (I want code using the Column Header Name)

 

[Prince27]

Appreciate for your reply.

I completely agree with your thoughts.
I tried creating tables and use of if then else , conditional formatting and or if conditions with few other options

1. the challenge i faced here is the size of the file is increase drastically as it has large data sets
2. The file has columns of 150 + and conditions or scenarios are more than 20 with interdependent 3 to 4 columns for couple of scenarios.
3. I have to create 10 to 15 files or folders every time i run the report which is completely manual effort or NVA work.


When i wrote the code in VB ( with your help on few scenarios) the time taken for the all the scenarios is less than a min for the entire data set.


As i am almost done with the coloring part, if possible help me on the filtering, creating folder and saving the files.

The above requirement (filtering and saving file to folders) is the final part of my main requirement.

I really appreciate and it would be be great if you can help on the same.

Thanks again for your time and thoughts . Below is more detail on the filtering saving part.

Lets say there are 2 different workbooks :

1 is Raw data workbook with only 1 tab or worksheet called Main Data

1 more workbook where i have or want the code and has only 1 worksheet named Mail Info



Requirement 1 :

In Mail Info workbook Column A ( A2:A50) i have a list of names like Bob, Jack, John, Sibu etc and Cell C7 has a drop down list with reference to column A (A2:A50)



In Raw data file, say i have columns Plan A, Plan B, Email, Plan C, Notes etc., Column Email has different names ( Bob, Jack, John etc)



In Mail Info file, Cell value C7 says Bob, then data related to Bob in the Raw data should be filtered along with formatting (as few of the cells will be colored) and should create a new folder called Bob ( with value in cell C7) and save as a new workbook ( raw data file has only 1 worksheet/tab) in that folder. If folder already exists only file should be saved in the respective folder.



Requirement 2:



In Mail Info file, cell value C7 says ALL, then what ever unique names available under Column Email of the Raw data file ( say there are 10 unique names) data to be filtered for all the unique names accordingly and 10 unique files and folders to created and saved accordingly.



Save as each file or workbook with unique name given under email column

 

[offthelip]

I am not the right person to help you with filtering in VBA, as I never use filtering in VBA, I always do it a different way, (just like with coloring cells using VBA which I never use) filtering involves using the worksheet and so is often slower than doing it entirely within VBA.