I can't get no, satisfaction... Hey, hey, hey...

[jaeb4u2c]

Okay, the Rolling Stones is one of my favorite groups and "Satisfaction" is one of my favorite songs. But I am really having a difficult time with an excel project and I could really use some help with. Let me explain:

I am going to paste a copy of the code that I have up to this point. What I am trying to do is take the color coding in a cell and give it a value of either "C" or "H" and put that value in an adjacent cell. Here are the cells that will display the color blue or red. And what I want to do is have that cell's color produce an "H" if it is red, or "C" if it is blue, and I want this character to appear in adjacent cells. For example:

Cells with red or blue colors ...............Cells where the "H" or "C" will appear
T9:T31 ................................................... AK9:AK31
U9:U31 .................................................. AL9:AL31
V9:V31 .................................................. AM9:AM31
W9:W31................................................. AN9:AN31
X9:X31................................................... AO9:AO31
Y9:Y31................................................... AP9:AP31


Private Sub Worksheet_Calculate()
' Multiple Conditional Format
Dim rng As Range
' Adjust Format range to suit
Set rng = Range("T9:T31, U9:U31, V9:V31, W9:W31, X9:X31, Y9:Y31")
' Adjust conditions to suit
For Each ce In rng
Select Case ce.Value
' BLUE
Case 1, 2, 3, 4, 5, 6, 9, 10, 11, 12, 13, 14, 19, 22, 23, 24, 26, 27, 30, 31, 34, 35, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 54, 55, 56
ce.Interior.ColorIndex = 3
' RED
Case 7, 8, 15, 16, 17, 18, 20, 21, 25, 28, 29, 32, 33, 36, 53
ce.Interior.ColorIndex = 5
' No Format
Case Is <> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56
ce.Interior.ColorIndex = 0
End Select
Next ce

End Sub

 

[Richard Schollar]

You don't actually ask a question in your post, so I'm assuming that your code currently works in applying the cell color to each cell? In which case you should be able to modify as follows:

Private Sub Worksheet_Calculate() 
' Multiple Conditional Format 
Dim rng As Range 
' Adjust Format range to suit 
Set rng = Range("T9:T31, U9:U31, V9:V31, W9:W31, X9:X31, Y9:Y31") 
' Adjust conditions to suit 
For Each ce In rng 
Select Case ce.Value 
' BLUE 
Case 1, 2, 3, 4, 5, 6, 9, 10, 11, 12, 13, 14, 19, 22, 23, 24, 26, 27, 30, 31, 34, 35, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 54, 55, 56 
ce.Interior.ColorIndex = 3 
ce.offset(0,17).Value = "C"
' RED 
Case 7, 8, 15, 16, 17, 18, 20, 21, 25, 28, 29, 32, 33, 36, 53 
ce.Interior.ColorIndex = 5 
ce.offset(0,17).Value = "H"
' No Format 
Case Else
ce.Interior.ColorIndex = 0 
End Select 
Next ce 

End Sub

This is untested.

Best regards

Richard

 

[Scott Huish]

It isn't quite clear what this code is supposed to do, the way it is written it would be looking for those numbers in each of the individual cells, which isn't specified in your description. What are you trying to do exactly?

 

[Von Pookie]

Untested:

Private Sub Worksheet_Calculate()
Dim rng As Range, ce As Range, col As Integer

Set rng = Range("T9:Y31")

For Each ce In rng
   With ce
      'determine what column to put results in
      Select Case .Column
         Case Is = 20 'if checking column T
            col = 37 'results in column AK
         Case Is = 21 'if checking column U
            col = 38 'results in column AL
         Case Is = 22 'if checking column V
            col = 39 'results in column AM
         Case Is = 23 'if checking column W
            col = 40 'results in column AN
         Case Is = 24 'if checking column X
            col = 41 'results in column AO
         Case Is = 25 'if checking column Y
            col = 42 'results in column AP
      End Select
      
      Select Case .Value
         ' BLUE
         Case 1 To 6, 9 To 14, 19, 22 To 24, 26, 27, 30, 31, 34, 35, 37 To 52, 54 to 56
            .Interior.ColorIndex = 3
            Cells(.Row, col).Value = "H"
         ' RED
         Case 7, 8, 15 To 18, 20, 21, 25, 28, 29, 32, 33, 36, 53
            .Interior.ColorIndex = 5
            Cells(.Row, col).Value = "C"
         ' No Format
         Case Else
            .Interior.ColorIndex = 0
            Cells(.Row, col).ClearContents
      End Select
   End With
Next ce

End Sub