change cell color depending on two cell values

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
 
C

Claus Busch

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
Click to expand...

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Junp start my Select Case Macro 12
VBA multiple list of values for variable 1
Trying to loop format on excel 1
Remove Identical words 0
using 2 different IF statements 2
Overlapping 2 charts (XL2003) 2
Dealing with the error: unable to filter on excel hc home school 0
The sum of the same function that is used for a range of cells 2

Top