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

[dleseward]

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: Forecast
DeliverableRef	MilestoneA-RAG	MilestoneA-Pcnt	MilestoneA	MilestoneB-RAG	MilestoneB-Pcnt	MilestoneB	MilestoneC-RAG	MilestoneC-Pcnt	MilestoneC
Deliv1	Red	0%	01-Jun-17	Red	0%	18-Jun-17	Green	0%	01-Jul-17
Deliv2	Amber	20%	20-Jun-17	Green	0%	25-Jun-17	Amber	0%	15-Jul-17
Deliv3	Green	80%	01-Jul-17	Amber	60%	15-Jul-17	Green	0%	01-Aug-17
Deliv4	Complete	100%	01-May-17	Complete	100%	20-Jun-17	Green	0%	01-Jul-17

<tbody>
</tbody>

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:

[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.

 

[dleseward]

I posted the example file I am working on https://www.dropbox.com/sh/tqj1cxsjqqfyqtf/AAD6XY_J9AmqSa8_bAU3Fr4ca?dl=0

 

[dleseward]

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