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>
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
You are very welcome & thanks for your kind words. :)
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

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