Conditional Formatting Based on Headers

[Zackf]

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 :

[TABLE="width: 256"]
<tbody>[TR]
[TD="width: 64"]Name[/TD]
[TD="width: 64"]Score[/TD]
[TD="width: 64"]Transfers[/TD]
[TD="width: 64"]Length[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[TD="align: right"]550[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD]Susie[/TD]
[TD="align: right"]325[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Raymond[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]88[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD="align: right"]499[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]28[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

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]

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]

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]

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]

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

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

 

[Zackf]

You're a gentleman and a scholar. Thank you!

 

[Peter_SSs]

You are very welcome & thanks for your kind words.