VBA Conditional Formatting Not Working Properly

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?

Code:

---

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
End Select
End If
End With

---

First impression is the the text is a number not a string

inBoxString = Worksheets(DATA_SHEET).Cells(i, 1) & " AT: " & Round(duration * 24, 2)

Inbox string contains both text and numbers.. Any thoughts?

It looks like whatever value i put as a condition for duration, the text is always aligned right..

Quote:

---

Originally Posted by Aussiebear

First impression is the the text is a number not a string

---

Code:

---

With Gantshape
  - - - -
  - - - -
  If inBoxString <> "" Then
    with .TextFrame.Characters
      .Text = inBoxString
      .Font.Size = PatternType.Parent.Font.Size
      .Font.FontStyle = PatternType.Parent.Font.Size
      .Font.Color = PatternType.Parent.Font.Color
    end with
    .TextFrame.VerticalAlignment = xlTop
    .TextFrame.HorizontalAlignment = iif(duration< choose(ascii(equipment)-64),1.5,.5,5),xlleft,xlright)
  end if
end with

---

Thanks heaps Mark! Much appreciated!

Do i keep select case? I have different pieces if equipment..

Also, right- left was just an example, i need to change font color and make the text bold. Would i keep the same code as you suggested?

Quote:

---

Originally Posted by snb

Code:

---

With Gantshape
  - - - -
  - - - -
  If inBoxString <> "" Then
    with .TextFrame.Characters
      .Text = inBoxString
      .Font.Size = PatternType.Parent.Font.Size
      .Font.FontStyle = PatternType.Parent.Font.Size
      .Font.Color = PatternType.Parent.Font.Color
    end with
    .TextFrame.VerticalAlignment = xlTop
    .TextFrame.HorizontalAlignment = iif(duration< choose(ascii(equipment)-64),1.5,.5,5),xlleft,xlright)
  end if
end with

---

---

Cheers! What about my cases?