Colors in Columns

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

Can anyone tell me what is wrong with this code.
It colors columns D, E and F with Blue, Red and Green.

But I want ONLY ColumnD colored Blue if D75 = "A" and E75
and F75 are blank. ONLY ColumnE colored Red if E75 = "B"
and A75 and F75 are blank and ONLY ColumnF colored Green
if F75 = "C" and D75 and E75 are blank. As it is all
columns are colored. The non colored columns should be
black.
Is there a better way to write this code?

If Range("D74") = "A" And _
Range("E74") = "" And Range("F74") = "" Then
Range("D75.D176").Select
Selection.Font.ColorIndex = 5
End If
If Range("E74") = "B" And _
Range("D74") = "" And Range("F74") = "" Then
Range("E75.E176").Select
Selection.Font.ColorIndex = 3
End If
If Range("F74") = "C" And _
Range("D74") = "" And Range("E74") = "" Then
Range("F75.F176").Select
Selection.Font.ColorIndex = 10
End If
 
maybe
If Range("D74") = "A" And _
Range("E74") = "" And Range("F74") = "" Then
Range("D75.D176").Font.ColorIndex = 5
exit sub
End If
If Range("E74") = "B" And _
Range("D74") = "" And Range("F74") = "" Then
Range("E75.E176").Font.ColorIndex = 3
exit sub
End If
If Range("F74") = "C" And _
Range("D74") = "" And Range("E74") = "" Then
Range("F75.F176").Font.ColorIndex = 10
End If
 
You need to include an 'Else' statement as the values are
mutually exclusive. I think you're testing for each color
and not returning the original font back to black:-

i.e

Sub colortest()

If Range("D74") = "A" And _
Range("E74") = "" And Range("F74") = "" Then
Range("D75.D176").Select
Selection.Font.ColorIndex = 5
Else
Selection.Font.ColorIndex = 1
End If
If Range("E74") = "B" And _
Range("D74") = "" And Range("F74") = "" Then
Range("E75.E176").Select
Selection.Font.ColorIndex = 3
Else
Selection.Font.ColorIndex = 1
End If
If Range("F74") = "C" And _
Range("D74") = "" And Range("E74") = "" Then
Range("F75.F176").Select
Selection.Font.ColorIndex = 10
Else
Selection.Font.ColorIndex = 1
End If


End Sub

hth,
Tony
 
Thanks for your help,
Regards,
Richard
-----Original Message-----
maybe
If Range("D74") = "A" And _
Range("E74") = "" And Range("F74") = "" Then
Range("D75.D176").Font.ColorIndex = 5
exit sub
End If
If Range("E74") = "B" And _
Range("D74") = "" And Range("F74") = "" Then
Range("E75.E176").Font.ColorIndex = 3
exit sub
End If
If Range("F74") = "C" And _
Range("D74") = "" And Range("E74") = "" Then
Range("F75.F176").Font.ColorIndex = 10
End If




.
Click to expand...
 
Thanks for your help, Tony
Regards,
Richard
-----Original Message-----
You need to include an 'Else' statement as the values are
mutually exclusive. I think you're testing for each color
and not returning the original font back to black:-

i.e

Sub colortest()

If Range("D74") = "A" And _
Range("E74") = "" And Range("F74") = "" Then
Range("D75.D176").Select
Selection.Font.ColorIndex = 5
Else
Selection.Font.ColorIndex = 1
End If
If Range("E74") = "B" And _
Range("D74") = "" And Range("F74") = "" Then
Range("E75.E176").Select
Selection.Font.ColorIndex = 3
Else
Selection.Font.ColorIndex = 1
End If
If Range("F74") = "C" And _
Range("D74") = "" And Range("E74") = "" Then
Range("F75.F176").Select
Selection.Font.ColorIndex = 10
Else
Selection.Font.ColorIndex = 1
End If


End Sub

hth,
Tony


.
Click to expand...
 
Back
Top