VBA Code for Conditional Formatting Not Working

Hi All,

I was trying to revise a code by adding bits for conditional formatting - if duration is less then a certain number text is aligned left, else its aligned right. The formatting is done within text boxes - shapes. Please see bits of the code below. Is there anything wrong there? Its not working properly as it always aligns the text to the right, never to the left. Any thoughts?

duration = Round((EndTime - StartTime) * 24 * 60)
Set GantShape = Worksheets(destSheet).Shapes.AddShape(msoShapeRectangle, LeftPoints, TopPoints, WidthPoints, HeightPoints)

With GantShape
.Fill.ForeColor.RGB = PatternType.Color

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

Select Case equipment
Case "A"
If duration < 1.5 Then
.TextFrame.Characters.Text = inBoxString
.TextFrame.Characters.Font.Size = PatternType.Parent.Font.Size
.TextFrame.Characters.Font.FontStyle = PatternType.Parent.Font.Size
.TextFrame.Characters.Font.Color = PatternType.Parent.Font.Color
.TextFrame.VerticalAlignment = xlTop
.TextFrame.HorizontalAlignment = xlLeft
Else
.TextFrame.Characters.Text = inBoxString
.TextFrame.Characters.Font.Size = PatternType.Parent.Font.Size
.TextFrame.Characters.Font.FontStyle = PatternType.Parent.Font.Size
.TextFrame.Characters.Font.Color = PatternType.Parent.Font.Color
.TextFrame.VerticalAlignment = xlTop
.TextFrame.HorizontalAlignment = xlRight

End If

Case "B"
If duration < 0.5 Then
.TextFrame.Characters.Text = inBoxString
.TextFrame.Characters.Font.Size = PatternType.Parent.Font.Size
.TextFrame.Characters.Font.FontStyle = PatternType.Parent.Font.Size
.TextFrame.Characters.Font.Color = PatternType.Parent.Font.Color
.TextFrame.VerticalAlignment = xlTop
.TextFrame.HorizontalAlignment = xlLeft
Else
.TextFrame.Characters.Text = inBoxString
.TextFrame.Characters.Font.Size = PatternType.Parent.Font.Size
.TextFrame.Characters.Font.FontStyle = PatternType.Parent.Font.Size
.TextFrame.Characters.Font.Color = PatternType.Parent.Font.Color
.TextFrame.VerticalAlignment = xlTop
.TextFrame.HorizontalAlignment = xlRight

End If

Case "C"
If duration < 5 Then
.TextFrame.Characters.Text = inBoxString
.TextFrame.Characters.Font.Size = PatternType.Parent.Font.Size
.TextFrame.Characters.Font.FontStyle = PatternType.Parent.Font.Size
.TextFrame.Characters.Font.Color = PatternType.Parent.Font.Color
.TextFrame.VerticalAlignment = xlTop
.TextFrame.HorizontalAlignment = xlLeft
Else
.TextFrame.Characters.Text = inBoxString
.TextFrame.Characters.Font.Size = PatternType.Parent.Font.Size
.TextFrame.Characters.Font.FontStyle = PatternType.Parent.Font.Size
.TextFrame.Characters.Font.Color = PatternType.Parent.Font.Color
.TextFrame.VerticalAlignment = xlTop
.TextFrame.HorizontalAlignment = xlRight

End If

Case "D"
If duration < 60 Then
.TextFrame.Characters.Text = inBoxString
.TextFrame.Characters.Font.Size = PatternType.Parent.Font.Size
.TextFrame.Characters.Font.FontStyle = PatternType.Parent.Font.Size
.TextFrame.Characters.Font.Color = PatternType.Parent.Font.Color
.TextFrame.VerticalAlignment = xlTop
.TextFrame.HorizontalAlignment = xlLeft
Else
.TextFrame.Characters.Text = inBoxString
.TextFrame.Characters.Font.Size = PatternType.Parent.Font.Size
.TextFrame.Characters.Font.FontStyle = PatternType.Parent.Font.Size
.TextFrame.Characters.Font.Color = PatternType.Parent.Font.Color
.TextFrame.VerticalAlignment = xlTop
.TextFrame.HorizontalAlignment = xlRight

End If

Case "E"
If duration * 24 < 0.1 Then
.TextFrame.Characters.Text = inBoxString
.TextFrame.Characters.Font.Size = PatternType.Parent.Font.Size
.TextFrame.Characters.Font.FontStyle = PatternType.Parent.Font.Size
.TextFrame.Characters.Font.Color = PatternType.Parent.Font.Color
.TextFrame.VerticalAlignment = xlTop
.TextFrame.HorizontalAlignment = xlLeft
Else
.TextFrame.Characters.Text = inBoxString
.TextFrame.Characters.Font.Size = PatternType.Parent.Font.Size
.TextFrame.Characters.Font.FontStyle = PatternType.Parent.Font.Size
.TextFrame.Characters.Font.Color = PatternType.Parent.Font.Color
.TextFrame.VerticalAlignment = xlTop
.TextFrame.HorizontalAlignment = xlRight

End If
End Select

End If
End With

Sorry for that, hope this below is better now. Any help would be much appreciated.

Please Login or Register  to view this content.

Originally Posted by Richard Buttrey

Hi, and welcome to the forum.
Unfortunately your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Please Login or Register  to view this content.

Originally Posted by Richard Buttrey

Hi,

Which equipment ref is giving you the wrong result?

Are you sure you want to multiply the time difference by 24 x 60. In the case of equipment A unless the time is difference is less than 90 seconds you'll never get left aligned text.

Hi, thank for the reply.

That's the problem, every piece of equipment is giving me a wrong result.

I actually want the text to be bold and red, not aligned right, this was just a test.