Conditional Formatting Based on Headers

Zackf

New Member
Joined
Oct 27, 2019
Messages
4
I am having trouble coming up with code that will scan the column headers, strings, and then apply the specific conditional formatting based on values in that column. The order of the columns can vary depending on how individuals pull the report. I want the formatting consistent despite the order of the columns. That's why I want it based on the header. Sometimes only certain items will be pulled and the codes needs to be able to ignore what doesn't need formatting. Dynamic in other words.


Here is an example though the sheet I'm building though it will likely have 10-15 columns :

NameScoreTransfersLength
Bob4001055
Sally5501531
Susie325840
Raymond6002088
James4991728

<tbody>
</tbody>
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,752
Office Version
365
Platform
Windows
Welcome to the MrExcel board!

You haven't said what you want formatted but see if this example helps.
Suppose you want the values highlighted ..
- If Scores are in the 400s
- If Transfers are less than 10
- If Length > 40
then you could build a rule like this

<b>CF Based on headers</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:74px;" /><col style="width:53px;" /><col style="width:72px;" /><col style="width:58px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">Name</td><td style="font-size:10pt; text-align:right; ">Score</td><td style="font-size:10pt; text-align:right; ">Transfers</td><td style="font-size:10pt; text-align:right; ">Length</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">Bob</td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">400</td><td style="font-size:10pt; text-align:right; ">10</td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">55</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">Sally</td><td style="font-size:10pt; text-align:right; ">550</td><td style="font-size:10pt; text-align:right; ">15</td><td style="font-size:10pt; text-align:right; ">31</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; ">Susie</td><td style="font-size:10pt; text-align:right; ">325</td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">8</td><td style="font-size:10pt; text-align:right; ">40</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; ">Raymond</td><td style="font-size:10pt; text-align:right; ">600</td><td style="font-size:10pt; text-align:right; ">20</td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">88</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; ">James</td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">499</td><td style="font-size:10pt; text-align:right; ">17</td><td style="font-size:10pt; text-align:right; ">28</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Conditional formatting </b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Cell</td><td >Nr.: / Condition</td><td >Format</td></tr><tr><td >B2</td><td >1. / Formula is =IF(B$1="Score",AND(B2>=400,B2<500),IF(B$1="Transfers",B2<10,IF(B$1="Length",B2>40)))</td><td style="background-color:#92d050; ">Abc</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4


If you want different colours for the various columns then you would break this up into individual rules.
If you needed help with that, or I have completely missed what you are trying to do, post back with more details.
 

Zackf

New Member
Joined
Oct 27, 2019
Messages
4
Thank you kindly for the response. I should have mentioned I was wanting VBA code. I'm creating a form with some more options. But to answer your questions there will be a series of between, greater and less than operators.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,752
Office Version
365
Platform
Windows
I should have mentioned I was wanting VBA code.
Yes, handy to know. ;)
Still are you wanting code to ..
- find the correct column (eg Transfers) and assess each value in that column and apply directly the formatting to that cell if it meets the condition?
or
- find the correct column and apply the relevant standard Conditional Formatting to that column (so that formatting updates automatically if values in the column change)?


If this is going to be done by vba, where & how is the information about the column headers and the particular 'CF rules' for that column going to be stored?
 

Zackf

New Member
Joined
Oct 27, 2019
Messages
4
Yes, handy to know. ;)
Still are you wanting code to ..
- find the correct column (eg Transfers) and assess each value in that column and apply directly the formatting to that cell if it meets the condition?
This is it! I was going to store this in the worksheet(1) code editor.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,752
Office Version
365
Platform
Windows
So, doing a similar thing to my previous example but directly with vba, you could try this.

I have assumed that ..
- The relevant headings will exist in the sheet
- The sheet has something in column A

I have shown 3 different ways that you might specify the particular colour for a column

Rich (BB code):
Sub Apply_Colour()
  Dim col As Long
  
  Application.ScreenUpdating = False
  With ActiveSheet.UsedRange
    .Parent.AutoFilterMode = False
    
    'Score in the 400s
    col = .Rows(1).Find(What:="Score", LookAt:=xlWhole, MatchCase:=False).Column
    .AutoFilter Field:=col, Criteria1:=">=400", Operator:=xlAnd, Criteria2:="<500"
    .Columns(col).SpecialCells(xlVisible).Interior.Color = RGB(120, 255, 100)
    .AutoFilter Field:=col
    
    'Transfers < 10
    col = .Rows(1).Find(What:="Transfers", LookAt:=xlWhole, MatchCase:=False).Column
    .AutoFilter Field:=col, Criteria1:="<10"
    .Columns(col).SpecialCells(xlVisible).Interior.Color = vbCyan
    .AutoFilter Field:=col
    
    'Length over 40
    col = .Rows(1).Find(What:="Length", LookAt:=xlWhole, MatchCase:=False).Column
    .AutoFilter Field:=col, Criteria1:=">40"
    .Columns(col).SpecialCells(xlVisible).Interior.Color = 186562
    .AutoFilter Field:=col
    
    'Remove colour from row 1 & remove autofilter
    .Rows(1).Interior.Color = xlNone
    .Parent.AutoFilterMode = False
  End With
  Application.ScreenUpdating = True
End Sub
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,752
Office Version
365
Platform
Windows
You are very welcome & thanks for your kind words. :)
 

Forum statistics

Threads
1,086,107
Messages
5,387,870
Members
402,084
Latest member
Nittanyblu

Some videos you may like

This Week's Hot Topics

Top