Apply Multiple Conditional Formatting to a Table Column based on values in other columns

D

dleseward

New Member
Joined
Apr 5, 2011
Messages
18
Hi I am trying to add icons and colours to a table column based on values in adjacent columns. I am trying to do this with VBA as that seems the only way to do it.

Basically In the table below, I want the column MilestoneA to be colored as per the column MilestoneA-RAG values and also contain the Icons as per the column MilestoneA-Pcnt. The objective is to be able to hide these two columns in a summary and just display the date column to show all three.

[TABLE="width: 1002"]
<tbody>[TR]
[TD]Table: Forecast[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DeliverableRef[/TD]
[TD]MilestoneA-RAG[/TD]
[TD]MilestoneA-Pcnt[/TD]
[TD]MilestoneA[/TD]
[TD]MilestoneB-RAG[/TD]
[TD]MilestoneB-Pcnt[/TD]
[TD]MilestoneB[/TD]
[TD]MilestoneC-RAG[/TD]
[TD]MilestoneC-Pcnt[/TD]
[TD]MilestoneC[/TD]
[/TR]
[TR]
[TD]Deliv1[/TD]
[TD]Red[/TD]
[TD]0%[/TD]
[TD]01-Jun-17[/TD]
[TD]Red[/TD]
[TD]0%[/TD]
[TD]18-Jun-17[/TD]
[TD]Green[/TD]
[TD]0%[/TD]
[TD]01-Jul-17[/TD]
[/TR]
[TR]
[TD]Deliv2[/TD]
[TD]Amber[/TD]
[TD]20%[/TD]
[TD]20-Jun-17[/TD]
[TD]Green[/TD]
[TD]0%[/TD]
[TD]25-Jun-17[/TD]
[TD]Amber[/TD]
[TD]0%[/TD]
[TD]15-Jul-17[/TD]
[/TR]
[TR]
[TD]Deliv3[/TD]
[TD]Green[/TD]
[TD]80%[/TD]
[TD]01-Jul-17[/TD]
[TD]Amber[/TD]
[TD]60%[/TD]
[TD]15-Jul-17[/TD]
[TD]Green[/TD]
[TD]0%[/TD]
[TD]01-Aug-17[/TD]
[/TR]
[TR]
[TD]Deliv4[/TD]
[TD]Complete[/TD]
[TD]100%[/TD]
[TD]01-May-17[/TD]
[TD]Complete[/TD]
[TD]100%[/TD]
[TD]20-Jun-17[/TD]
[TD]Green[/TD]
[TD]0%[/TD]
[TD]01-Jul-17[/TD]
[/TR]
</tbody>[/TABLE]

I have been going through latest and older copies of MrExcel's VBA books as well as some other references, but none show how to do anything like this, at least not with Tables. Any help would be welcome. While I am trying this in Excel 2016, I will need to make it work in Excel 2010.

The VBA code I have so far (which fails) just trying to apply the icons follows (it is attempting to apply the approach in VBA & Macros for Excel 2010:

Code: 
[COLOR=#000000][FONT=Menlo]Sub FormatMilestones()[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]   [COLOR=#011993]Dim[/COLOR] fTable [COLOR=#011993]As[/COLOR] ListObject[/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo]Dim[COLOR=#000000] fCol [/COLOR]AsInteger[COLOR=#000000], fRow [/COLOR]AsInteger[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]   [COLOR=#011993]Dim[/COLOR] fColName [COLOR=#011993]As[/COLOR] [COLOR=#011993]String[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]   [COLOR=#011993]Dim[/COLOR] mileStoneA [COLOR=#011993]As[/COLOR] [COLOR=#011993]String[/COLOR], mileStoneB [COLOR=#011993]As[/COLOR] [COLOR=#011993]String[/COLOR], mileStoneC [COLOR=#011993]As[/COLOR] [COLOR=#011993]String[/COLOR][/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo]Dim[COLOR=#000000] ragR [/COLOR]AsString[COLOR=#000000], ragA [/COLOR]AsString[COLOR=#000000], ragG [/COLOR]AsString[COLOR=#000000], ragC [/COLOR]AsString[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]   [COLOR=#011993]Dim[/COLOR] formatRange [COLOR=#011993]As[/COLOR] Range[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]   [COLOR=#011993]Dim[/COLOR] ICS [COLOR=#011993]As[/COLOR] IconSetCondition[/FONT][/COLOR]

[COLOR=#000000][FONT=Menlo]   [COLOR=#011993]Set[/COLOR] fTable = ThisWorkbook.ActiveSheet.ListObjects("Forecast")[/FONT][/COLOR]


[COLOR=#000000][FONT=Menlo]   mileStoneA = "MilestoneA"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]   mileStoneB = "MilestoneB"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]   mileStoneC = "MilestoneC"[/FONT][/COLOR]

[COLOR=#000000][FONT=Menlo]   ragR = "Red"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]   ragA = "Amber"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]   ragG = "Green"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]   ragC = "Complete"[/FONT][/COLOR]


[COLOR=#000000][FONT=Menlo]   [COLOR=#011993]For[/COLOR] fCol = 1 [COLOR=#011993]To[/COLOR] fTable.ListColumns.Count[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]      fColName = fTable.HeaderRowRange(fCol).Value[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]      [COLOR=#011993]If[/COLOR] fColName = mileStoneA [COLOR=#011993]Or[/COLOR] fColName = mileStoneB [COLOR=#011993]Or[/COLOR] fColName = mileStoneC [COLOR=#011993]Then[/COLOR][/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo]' Set conditional formatting conditions[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]        [COLOR=#011993]Set[/COLOR] formatRange = fTable.ListColumns(fCol).Range[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]        [COLOR=#011993]With[/COLOR] formatRange[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]            .FormatConditions.Delete[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]            [COLOR=#011993]Set[/COLOR] ICS = .FormatConditions.AddIconSetCondition()[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]        [COLOR=#011993]End[/COLOR] [COLOR=#011993]With[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]        [COLOR=#011993]With[/COLOR] ICS[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]            .ReverseOrder = [COLOR=#011993]False[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]            .ShowIconOnly = [COLOR=#011993]False[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]            .IconSet = ActiveWorkbook.IconSets(xl5CRV)[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]        [COLOR=#011993]End[/COLOR] [COLOR=#011993]With[/COLOR][/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo]'Set Icon based on Percent Column (fCol -1) Value[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]        [COLOR=#011993]With[/COLOR] formatRange[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]            .Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]            .FormatConditions.Delete[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]            .FormatConditions.AddIconSet[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]            .Formula1 = "fTable.ListColumns(fCol-1).Range.Select"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]        [COLOR=#011993]End[/COLOR] [COLOR=#011993]With[/COLOR][/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo]'Set Colour based on RAG in fCol -2[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#000000][FONT=Menlo]      [COLOR=#011993]End[/COLOR] [COLOR=#011993]If[/COLOR][/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo]Next[/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo]EndSub[/FONT][/COLOR]
I am curious why the reference books make so little reference to using Excel Tables as they are so much easier to work with.

In any case if anyone can help with this I'd be very grateful.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
I got part of the way there using this function to work on each cell. I just can't get the formula (bold bit) to work to use the value in the other cell. Every variation I have tried either does something with the cell value itself or ends-up replacing the cell value (not what intended). I have scoured all the MrExcel VBA books but don't see anything to help.

Sub SetIcon(IconSetRange As Range, IconConditionRange As Range)
Dim ICS As IconSetCondition
With IconSetRange
.Select
.FormatConditions.Delete
Set ICS = .FormatConditions.AddIconSetCondition()
With ICS
.ReverseOrder = False
.ShowIconOnly = False
.IconSet = ActiveWorkbook.IconSets(xl5CRV)
End With
With ICS.IconCriteria(2)
.Type = xlConditionValuePercent
.Value = 25
End With
With ICS.IconCriteria(3)
.Type = xlConditionValuePercent
.Value = 50
End With
With ICS.IconCriteria(4)
.Type = xlConditionValuePercent
.Value = 75
End With
With ICS.IconCriteria(5)
.Type = xlConditionValuePercent
.Value = 90
End With
.FormatConditions.AddIconSetCondition
.Formula = "=IconConditionRange.Value" '>> seems to set the cells to this actual value
EndWith
EndSub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
VBA hides/unhides columns based upon pivot table and value
Replies
0
Views
356
mapatton
M
O
  • Solved
How can I loop ".Resize Range()" to dynamically adjust a table range?
Replies
7
Views
2K
Alex Blakenburg
A
Nuke_It_Newport
  • Solved
Conditional Formatting Rules Not Applying
Replies
1
Views
898
Nuke_It_Newport
Nuke_It_Newport
duteberta
  • Question Question
VBA code to auto sort table based on any change within a specific column
2
Replies
17
Views
1K
Peter_SSs
Peter_SSs
J
  • Question Question
VBA: Conditional Formatting one column based on another
Replies
2
Views
425
jazzhayward
J

Forum statistics

Threads
1,223,893
Messages
6,175,239
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

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