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:
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.
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]
In any case if anyone can help with this I'd be very grateful.