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)

 

[afonsomira]

Hello could you attach the file, so I can help you better.

 

[offthelip]

your requirement are a bit vague, "the rest of the cells should be changed to yellow" can be interpreted in a number of ways!! You will see what my interpretation is!!
Your item 2 doesn't make sense at all to me so I have ignored it, to me is says don't color any cells, so I haven't!!
However this code might get your started:

Sub test()
inarr = ActiveSheet.UsedRange
For i = 1 To UBound(inarr, 2)
  If inarr(1, i) = "Plan A" Then
   For j = 2 To UBound(inarr, 1)
      If (inarr(j, i) <> "Hard") Then
      If (inarr(j, i) <> "Soft") Then
       ' color the rest of the cells yellow whatever this means!!
        Range(Cells(j, i), Cells(UBound(inarr, 1), i)).Select
        With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
       End With
       Exit For
      End If
      End If
   Next j
  End If
 Next i
End Sub

 

[Prince27]

Sorry for confusion.
Below is my requirement:
Scenario 1 - Plan A column should only have 'Hard' or 'Soft', else rest of the cells should colored to yellow(example)
Scenario 2 - If Plan A column has other text other than 'Hard' or 'Soft' then it should check for Plan B columns for verbiage 'Hard' or 'Soft else the cells in Plan A column should colored to yellow(example)

Here are screen shots of pre and post running the code.
I'm need code even columns interchanged anywhere in the sheet.

In the screen shot Plan A column has 2 'Electronic', but only 1 'Electronic' cell is colored, because Plan B column for 1 of the 'Electronic' cell is mentioned as 'Hard'

Hope this time I have not confused. If you fell still confused just gimme the solution just for scenario 2 .

Appreciate your help !!

 

[offthelip]

That is a bit clearer try this:

Sub test()
inarr = ActiveSheet.UsedRange
For i = 1 To UBound(inarr, 2)
  If inarr(1, i) = "Plan A" Then
   acol = i
  End If
  If inarr(1, i) = "Plan B" Then
   bcol = i
  End If
 Next i
   For j = 2 To UBound(inarr, 1)
      If (inarr(j, acol) <> "Hard") Then
      If (inarr(j, acol) <> "Soft") Then
      If inarr(j, bcol) <> "Hard" Then
      If inarr(j, bcol) <> "Soft" Then
       
       ' color the rest of the cells yellow whatever this means!!
        Range(Cells(j, acol), Cells(j, acol)).Select
        With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
       End With
       Exit For
      End If
      End If
      End If
      End If
   Next j
  
 
End Sub

 

[Prince27]

Its not looping for the entire column, I mean if I have 10 rows, 5th, 8th & 10th rows have the Words other than 'Hard' or 'Soft' and the Plan B column doesnt have any 'Hard or 'Soft'. In this case 5,8,10th rows/cells should be colored, but only 5th row/cell is getting colored. It's not looping the entire column.

 

[offthelip]

Sorry, I for got to delete the "EXIT FOR" . just delete this line ,

 

[Prince27]

Thank you so much. it worked perfectly

 

[Prince27]

Actually i am working on large report with many conditions. Hence i am posting bits and pieces of the code requirements so that its easy for me to modify accordingly. Here are few more scenarios :

I have 2 sheets Raw Data & Mail Info


1. In the raw data sheet i have colums like Plan A, Plan B, Mail, Plan C, Time 1, Notes, Time 2 and so on. In ‘Mail Info’ sheet column D (cell D7) has drop down list with unique names given in column A (A2:A30) like Bob, Sibu, Alan, Denly, All etc.,
If cell D7 says Sibu, I need a code to create folder with sibu and filter data in the raw data sheet with sibu and create a new workbook with name as given in cell D7 + current date & time and save in the respective folder of a given path. If folder name already exists just new workbook should be created.
For Filtering data refer to Column name ‘Mail’ in the raw data sheet.

In above scenario we are filtering data for each individual based on cell D7 value and creating new folder and save as new workbook in respective folder.

2. If cell D7 says All, then i want data to be filtered with all unique names given in raw data sheet of Column ‘Mail’ and create respective folders & workbooks and save as accordingly. If folder already exists just respective workbooks should be saved.

Here in one go am creating respective folders and workbooks for all the individuals

3. If column Plan A has the word ‘Hard’ then column Time 1 should have some days mentioned in it like ( 1day, 2minutes, 4hours , 1 month, 9 + days , 0 + days etc.,) If Time 1 column has blank cells or says NA or just any text without number, then ‘Notes’ column should have the words like (No hard, do not need , dont copy etc ) then it is fine else Column Plan A should be colored to green. There is inter dependency of Plan A , Time 1 and Notes column

I have written a code using if else, And Not condition with wild card and partial match like If cell value has (“*No hard*”) (“*do not need*”) etc.,


Here the challenge is the code is hard code which is restricted with few scenarios

What i am looking is i want the code to be dynamic.
In Mail Info sheet column E range E2:E20 i want to put all the scenarios in each cell like No hard, Do not need, dont keep etc so that whenever new scenarios come end user can add the wordings here in column E. (kind of a database).


****
i tried putting the requirements as clear as i can.
As the policy not allows me to share file, hence i am putting just the requirements.
As i said the data is large and scenarios are plenty. Based on your help with code am replicating to my actual requirements.

Appreciate your help on the above.
Thank you all the experts for your support and thoughts.

 

[offthelip]

It is perfectly possible to design a system where the logic rules are determined by data items which are held on a worksheet. ( quite often a hidden worksheet) this is particularly good when you have a workbook where you know the rules are likely to change. You do this by using tables, where you define a table with the possible words you might get in one cell , ( e.g. HARD, SOFT, N/A, then for each one of these you have another tables that tells you what possible values one of the other columns can take, you can take this idea and reuse it many times and thus develop some really complex logic ideas . You can then add extra conditions just by adding lines to the first table and then extra tables to deal with each line.
Finally if you are designing a large complex system , then it is NOT a good idea to use colors to indicate any logical condition. I did think about mentioning this right at the start of this thread. The reason it is a poor idea is that to change the color of inidivdual cells is a very slow process with VBA ( or by hand) it is much much faster to use and extra column with a true or false in it to show a condition and then use conditional formatting on that column. This can then be dealt with using vba very easily and quickly.