PDA

View Full Version : VBA Conditional Formatting Not Working Properly



lukazi
02-07-2014, 07:29 PM
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
End Select
End If
End With

Aussiebear
02-07-2014, 10:04 PM
First impression is the the text is a number not a string

lukazi
02-07-2014, 11:33 PM
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..


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

snb
02-08-2014, 07:53 AM
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

lukazi
02-08-2014, 08:13 AM
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?



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

lukazi
02-09-2014, 02:31 AM
Cheers! What about my cases?