change cell color depending on two cell values

  • Thread starter Thread starter Kevin Carter
  • Start date Start date
K

Kevin Carter

Hi
I have a worksheet that contains rows of colored cells depending on the result of two cell values.
The rows are letters from A to Z and the columns are W to A
so if the user enter coloum value W and row value B i want a cell h4 to turn Red. The range that the colors apply to change on each colunm
here is the code i wrote for one column, this is very long
for example in column a it the operator selects any row value up to letter E the cells are red in column B the operator enter a value up to the letter D
is there anyway i can shorten the code
If Range("C4").Value = "W" And Range("C5").Value = "A" And Range("ab1") = 0 Then
Range("D4").Value = Range("C4").Value
Range("D5").Value = Range("C5").Value
Range("Ab1").Value = 1
Range("I4").Value = "RED And RED"
Range("H4").Select
Call Colred
Range("C4").Select
Range("c4:c5").Select
Selection.ClearContents
Call NOFILL
End If
If Range("C4").Value = "W" And Range("C5").Value = "B" And Range("ab1") = 0 Then
Range("D4").Value = Range("C4").Value
Range("D5").Value = Range("C5").Value
Range("Ab1").Value = 1
Range("I4").Value = "RED And RED"
Range("H4").Select
Call Colred
Range("C4").Select
Range("c4:c5").Select
Selection.ClearContents
Call NOFILL
End If
If Range("C4").Value = "W" And Range("C5").Value = "C" And Range("ab1") = 0 Then
Range("D4").Value = Range("C4").Value
Range("D5").Value = Range("C5").Value
Range("Ab1").Value = 1
Range("I4").Value = "RED And RED"
Range("H4").Select
Call Colred
Range("C4").Select
Range("c4:c5").Select
Selection.ClearContents
Call NOFILL
End If
If Range("C4").Value = "W" And Range("C5").Value = "D" And Range("ab1") = 0 Then
Range("D4").Value = Range("C4").Value
Range("D5").Value = Range("C5").Value
Range("Ab1").Value = 1
Range("I4").Value = "RED And RED"
Range("H4").Select
Call Colred
Range("C4").Select
Range("c4:c5").Select
Selection.ClearContents
Call NOFILL
End If
If Range("C4").Value = "W" And Range("C5").Value = "E" And Range("ab1") = 0 Then
Range("D4").Value = Range("C4").Value
Range("D5").Value = Range("C5").Value
Range("Ab1").Value = 1
Range("I4").Value = "RED And RED"
Range("H4").Select
Call Colred
Range("C4").Select
Range("c4:c5").Select
Selection.ClearContents
Call NOFILL
End If

If Range("C4").Value = "W" And Range("C5").Value = "F" And Range("ab1") = 0 Then
Range("D4").Value = Range("C4").Value
Range("D5").Value = Range("C5").Value
Range("Ab1").Value = 1
Range("I4").Value = "RED And BLUE"
Range("H4").Select
Call COLPURP
Range("C4").Select
Range("c4:c5").Select
Selection.ClearContents
Call NOFILL
End If
If Range("C4").Value = "W" And Range("C5").Value = "G" And Range("ab1") = 0 Then
Range("D4").Value = Range("C4").Value
Range("D5").Value = Range("C5").Value
Range("Ab1").Value = 1
Range("I4").Value = "RED And BLUE"
Range("H4").Select
Call COLPURP
Range("C4").Select
Range("c4:c5").Select
Selection.ClearContents
Call NOFILL
End If
If Range("C4").Value = "W" And Range("C5").Value = "H" And Range("ab1") = 0 Then
Range("D4").Value = Range("C4").Value
Range("D5").Value = Range("C5").Value
Range("Ab1").Value = 1
Range("I4").Value = "RED And BLUE"
Range("H4").Select
Call COLPURP
Range("C4").Select
Range("c4:c5").Select
Selection.ClearContents
Call NOFILL
End If
If Range("C4").Value = "W" And Range("C5").Value = "J" And Range("ab1") = 0 Then
Range("D4").Value = Range("C4").Value
Range("D5").Value = Range("C5").Value
Range("Ab1").Value = 1
Range("I4").Value = "RED And BLUE"
Range("H4").Select
Call COLPURP
Range("C4").Select
Range("c4:c5").Select
Selection.ClearContents
Call NOFILL
End If
If Range("C4").Value = "W" And Range("C5").Value = "K" And Range("ab1") = 0 Then
Range("D4").Value = Range("C4").Value
Range("D5").Value = Range("C5").Value
Range("Ab1").Value = 1
Range("I4").Value = "RED And BLUE"
Range("H4").Select
Call COLPURP
Range("C4").Select
Call NOFILL
End If
If Range("C4").Value = "W" And Range("C5").Value = "L" And Range("ab1") = 0 Then
Range("D4").Value = Range("C4").Value
Range("D5").Value = Range("C5").Value
Range("Ab1").Value = 1
Range("I4").Value = "RED And BLUE"
Range("H4").Select
Call COLPURP
Range("C4").Select
Range("c4:c5").Select
Selection.ClearContents
Call NOFILL
End If
If Range("C4").Value = "W" And Range("C5").Value = "M" And Range("ab1") = 0 Then
Range("D4").Value = Range("C4").Value
Range("Ab1").Value = 1
Range("D5").Value = Range("C5").Value
Range("I4").Value = "RED And BLUE"
Range("H4").Select
Call COLPURP
Range("C4").Select
Range("c4:c5").Select
Selection.ClearContents
Call NOFILL
End If
If Range("C4").Value = "W" And Range("C5").Value = "N" And Range("ab1") = 0 Then
Range("D4").Value = Range("C4").Value
Range("D5").Value = Range("C5").Value
Range("Ab1").Value = 1
Range("I4").Value = "Blue And Blue"
Range("H4").Select
Call blueblue
Range("C4").Select
Range("c4:c5").Select
Selection.ClearContents
Call NOFILL
End If
If Range("C4").Value = "W" And Range("C5").Value = "P" And Range("ab1") = 0 Then
Range("D4").Value = Range("C4").Value
Range("D5").Value = Range("C5").Value
Range("Ab1").Value = 1
Range("I4").Value = "Blue And Blue"
Range("H4").Select
Call blueblue
Range("C4").Select
Range("c4:c5").Select
Selection.ClearContents
Call NOFILL
End If
If Range("C4").Value = "W" And Range("C5").Value = "R" And Range("ab1") = 0 Then
Range("D4").Value = Range("C4").Value
Range("D5").Value = Range("C5").Value
Range("Ab1").Value = 1
Range("I4").Value = "Blue And Blue"
Range("H4").Select
Call blueblue
Range("C4").Select
Range("c4:c5").Select
Selection.ClearContents
Call NOFILL
End If
If Range("C4").Value = "W" And Range("C5").Value = "S" And Range("ab1") = 0 Then
Range("D4").Value = Range("C4").Value
Range("D5").Value = Range("C5").Value
Range("Ab1").Value = 1
Range("I4").Value = "Blue And Blue"
Range("H4").Select
Call blueblue
Range("C4").Select
Range("c4:c5").Select
Selection.ClearContents
Call NOFILL
End If
If Range("C4").Value = "W" And Range("C5").Value = "T" And Range("ab1") = 0 Then
Range("D4").Value = Range("C4").Value
Range("D5").Value = Range("C5").Value
Range("Ab1").Value = 1
Range("I4").Value = "Blue And Blue"
Range("H4").Select
Call blueblue
Range("C4").Select
Range("c4:c5").Select
Selection.ClearContents
Call NOFILL
End If
If Range("C4").Value = "W" And Range("C5").Value = "V" And Range("ab1") = 0 Then
Range("D4").Value = Range("C4").Value
Range("D5").Value = Range("C5").Value
Range("Ab1").Value = 1
Range("I4").Value = "Blue And Blue"
Range("H4").Select
Call blueblue
Range("C4").Select
Range("c4:c5").Select
Selection.ClearContents
Call NOFILL
End If
If Range("C4").Value = "W" And Range("C5").Value = "W" And Range("ab1") = 0 Then
Range("D4").Value = Range("C4").Value
Range("D5").Value = Range("C5").Value
Range("Ab1").Value = 1
Range("I4").Value = "Blue And Blue"
Range("H4").Select
Call blueblue
Range("C4").Select
Range("c4:c5").Select
Selection.ClearContents
Call NOFILL
End If
If Range("C4").Value = "W" And Range("C5").Value = "X" And Range("ab1") = 0 Then
Range("D4").Value = Range("C4").Value
Range("D5").Value = Range("C5").Value
Range("Ab1").Value = 1
Range("I4").Value = "Blue And Blue"
Range("H4").Select
Call blueblue
Range("C4").Select
Range("c4:c5").Select
Selection.ClearContents
Call NOFILL
End If
If Range("C4").Value = "W" And Range("C5").Value = "Y" And Range("ab1") = 0 Then
Range("D4").Value = Range("C4").Value
Range("D5").Value = Range("C5").Value
Range("Ab1").Value = 1
Range("I4").Value = "Blue And Green"
Range("H4").Select
Call bluegreen
Range("C4").Select
Range("c4:c5").Select
Selection.ClearContents
Call NOFILL
End If
If Range("C4").Value = "W" And Range("C5").Value = "Z" And Range("ab1") = 0 Then
Range("D4").Value = Range("C4").Value
Range("D5").Value = Range("C5").Value
Range("Ab1").Value = 1
Range("I4").Value = "Blue And Green"
Range("H4").Select
Call bluegreen
Range("C4").Select
Range("c4:c5").Select
Selection.ClearContents
Call NOFILL
End If
 
Hi Kevin,

Am Thu, 5 Dec 2013 12:50:51 -0800 (PST) schrieb Kevin Carter:
I have a worksheet that contains rows of colored cells depending on the result of two cell values.
The rows are letters from A to Z and the columns are W to A
so if the user enter coloum value W and row value B i want a cell h4 to turn Red. The range that the colors apply to change on each colunm
here is the code i wrote for one column, this is very long
for example in column a it the operator selects any row value up to letter E the cells are red in column B the operator enter a value up to the letter D
is there anyway i can shorten the code

You can hard code the color for H4 and don't need to call other
procedures.
I have only the first 2 conditions so you have to enlarge the code:

Dim myStr As String
Dim myColor As Integer

Select Case Range("C5").Value
Case "A", "B", "C", "D", "E"
If Range("C4") = "W" And Range("AB1") = 0 Then
myStr = "RED And RED"
myColor = 13
End If
Case "F", "G", "H", "J", "K", "L", "M"
If Range("C4") = "W" And Range("AB1") = 0 Then
myStr = "BLUE And RED"
myColor = 13
End If
End Select
Range("D4") = Range("C4")
Range("D5") = Range("C5")
Range("H4").Interior.ColorIndex = myColor
Range("I4") = myStr
Range("AB1") = 1
Range("C4:C5").ClearContents
Range("C4:C5").ClearFormats



Regards
Claus B.
 
Back
Top