Additional to my previous post:
Trying my best to format my table but can't figure out how.
Condition (Stock Market data):
- From "P" column onward:
-- Empty cells = yellow fill
-- If the previous price < closing price --> closing price font is green
-- If the previous price > closing price --> closing price font is red
-- If the previous price = closing price --> previous and closing price are blue
Note that I have more than 200 columns.
Help me, please ...
I attached a screenshot for clarification.
Trying my best to format my table but can't figure out how.
Code:
Dim UsdCols As Long
UsdCols = Cells(6, Columns.Count).End(xlToLeft).Column
With Range("P6:P500")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($P6<>"""",$P6<$Q6)"
With .FormatConditions(.FormatConditions.Count)
.SetFirstPriority
.Font.Color = vbRed
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($P6<>"""",$P6>$Q6)"
With .FormatConditions(.FormatConditions.Count)
.SetFirstPriority
'.Font.Color = vbGreen
.Font.ColorIndex = 10
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($P6<>"""",$P6=$Q6)"
With .FormatConditions(.FormatConditions.Count)
.SetFirstPriority
.Font.Color = vbBlue
End With
End With
With Range("Q6", Cells(500, UsdCols))
.FormatConditions.Delete
'Empty cells = Yellow
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$P6="""""
With .FormatConditions(.FormatConditions.Count)
.SetFirstPriority
'.Font.Color = vbGreen
.Interior.ColorIndex = 19
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(Q6<>"""",Q6>P6)"
With .FormatConditions(.FormatConditions.Count)
.SetFirstPriority
'.Font.Color = vbGreen
.Font.ColorIndex = 10
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(Q6<>"""",Q6<P6)"
With .FormatConditions(.FormatConditions.Count)
.SetFirstPriority
.Font.Color = vbRed
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(Q6<>"""",Q6=P6)"
With .FormatConditions(.FormatConditions.Count)
.SetFirstPriority
.Font.Color = vbBlue
End With
End With
Condition (Stock Market data):
- From "P" column onward:
-- Empty cells = yellow fill
-- If the previous price < closing price --> closing price font is green
-- If the previous price > closing price --> closing price font is red
-- If the previous price = closing price --> previous and closing price are blue
Note that I have more than 200 columns.
Help me, please ...
I attached a screenshot for clarification.