Code for conditional formatting within Gantt Chart

lukazi

New Member
Joined
Feb 2, 2014
Messages
18
Hi guys,

I have this VBA problem to solve, and as Im new to VBA, your help would be priceless.

I have already a code developed thats drawing a Gantt chart based on different data. For those unfamiliar with Gantt charts, its a graphical presentation with shapes and boxes of different colors.

In each of those boxes there is a string similar to this one: AB = 2.72. I need to apply conditional formating to make the figure red if it exceeds 2. It makes it harder for me that the string is within the box, as its not refering to any particular cell. As the code is quite long, I'll just point out the most important parts of it that I think need to be used for conditional formating.

The part about the string within the box is:

inBoxString = " AB: " & Round((Worksheets(RAKE_DATA_SHEET).Cells(i, RAKE_DATA_ENDTIME_COL) - Worksheets(RAKE_DATA_SHEET).Cells(i, RAKE_DATA_STARTTIME_COL)) * 24, 2)

This inboxtring is placed within the shape, and it looks smth like: AB = 2.7. The value 2.7 is this part of the code from above:

Round((Worksheets(RAKE_DATA_SHEET).Cells(i, RAKE_DATA_ENDTIME_COL) - Worksheets(RAKE_DATA_SHEET).Cells(i, RAKE_DATA_STARTTIME_COL)) * 24, 2)

Where:
Worksheets(RAKE_DATA_SHEET).Cells(i, RAKE_DATA_ENDTIME_COL)

and:
Worksheets(RAKE_DATA_SHEET).Cells(i, RAKE_DATA_STARTTIME_COL)

are dates, and the difference is giving a duration (2.7). The codes is much longer than this, so please let me know if I need to go into more details.

Would you please be able to help me with this?
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

lukazi

New Member
Joined
Feb 2, 2014
Messages
18
There is also this sub in the code that refers to formatting (where interiortext refers to the text inside the shape that I want formatted):

Sub formatShape(GantShape As Shape, MyPattern As Interior, interiorText As String)
On Error GoTo ErrorTag
With GantShape
.Fill.ForeColor.RGB = MyPattern.Color

.Line.Weight = 0.25
.Name = "WorkingShape"
If interiorText <> "" Then

.TextFrame.Characters.Text = interiorText
.TextFrame.Characters.Font.Size = MyPattern.Parent.Font.Size
.TextFrame.Characters.Font.FontStyle = MyPattern.Parent.Font.Size
.TextFrame.Characters.Font.Color = MyPattern.Parent.Font.Color
.TextFrame.VerticalAlignment = xlTop
.TextFrame.HorizontalAlignment = xlLeft

End With
Not sure if I should use this sub to add bits about formatting?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,961
Messages
5,599,061
Members
414,281
Latest member
Engjamal2021

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
Top