|
Colors in Columns
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 |
Colors in Columns
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 "Richard" wrote in message ... 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 |
Colors in Columns
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 -----Original Message----- 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 . |
Colors in Columns
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 "Richard" wrote in message ... 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 . |
Colors in Columns
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 -----Original Message----- 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 . . |
| All times are GMT +1. The time now is 02:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com