Thread: Select Case help
View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Gary Keramidas Gary Keramidas is offline
external usenet poster
  
Posts: 2,494
Default Select Case help
did a search and replace and this seems to be ok. give it a try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Not Intersect(Target, Range("c7:dj52")) Is Nothing Then
If UCase(Target.Value) = "AL" Then
Target.Interior.ColorIndex = 3
ElseIf UCase(Target.Value) = "SL" Then
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = "FL" Then
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = "ML" Then
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = "DL" Then
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = "WL" Then
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = "OL" Then
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = "CL" Then
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = "PL" Then
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = "JD" Then
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = "X" Then
Target.Interior.ColorIndex = 15
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = "HO" Then
Target.Interior.ColorIndex = 15
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = "00" Then
Target.Interior.ColorIndex = 20
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = "01" Then
Target.Interior.ColorIndex = 20
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = "02" Then
Target.Interior.ColorIndex = 20
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = "03" Then
Target.Interior.ColorIndex = 20
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = "04" Then
Target.Interior.ColorIndex = 19
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = "05" Then
Target.Interior.ColorIndex = 19
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = "06" Then
Target.Interior.ColorIndex = 19
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = "07" Then
Target.Interior.ColorIndex = 19
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = "08" Then
Target.Interior.ColorIndex = 19
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = "09" Then
Target.Interior.ColorIndex = 19
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = 10 Then
Target.Interior.ColorIndex = 19
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = 11 Then
Target.Interior.ColorIndex = 19
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = 12 Then
Target.Interior.ColorIndex = 17
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = 13 Then
Target.Interior.ColorIndex = 17
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = 14 Then
Target.Interior.ColorIndex = 17
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = 15 Then
Target.Interior.ColorIndex = 17
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = 16 Then
Target.Interior.ColorIndex = 17
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = 17 Then
Target.Interior.ColorIndex = 17
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = 18 Then
Target.Interior.ColorIndex = 17
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = 19 Then
Target.Interior.ColorIndex = 20
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = 20 Then
Target.Interior.ColorIndex = 20
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = 21 Then
Target.Interior.ColorIndex = 20
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = 22 Then
Target.Interior.ColorIndex = 20
Target.Font.ColorIndex = 1

ElseIf UCase(Target.Value) = 23 Then
Target.Interior.ColorIndex = 20
Target.Font.ColorIndex = 1


ElseIf UCase(Target.Value) = "HO" Then
Target.Interior.ColorIndex = 15
Target.Font.ColorIndex = 2

ElseIf UCase(Target.Value) = "T" Then
Target.Interior.ColorIndex = 4
Target.Font.ColorIndex = 3

ElseIf UCase(Target.Value) = "<T" Then
Target.Interior.ColorIndex = 4
Target.Font.ColorIndex = 3

ElseIf UCase(Target.Value) = "OP" Then
Target.Interior.ColorIndex = 4
Target.Font.ColorIndex = 3

ElseIf UCase(Target.Value) = "TR" Then
Target.Interior.ColorIndex = 4
Target.Font.ColorIndex = 3

ElseIf UCase(Target.Value) = "AD" Then
Target.Interior.ColorIndex = 4
Target.Font.ColorIndex = 3

ElseIf UCase(Target.Value) = "MS" Then
Target.Interior.ColorIndex = 4
Target.Font.ColorIndex = 3

ElseIf UCase(Target.Value) = "TD" Then
Target.Interior.ColorIndex = 4
Target.Font.ColorIndex = 3

ElseIf UCase(Target.Value) = "Null" Then
Target.Interior.ColorIndex = 16
Target.Font.ColorIndex = 1

Else
Target.Interior.ColorIndex = 0
Target.Font.ColorIndex = 1

End If


End If
End If
End Sub



--


Gary


"Ramthebuffs"
wrote in message
...

I'm running a macro that has a select case in it. Some of the cases
have the same procedures. I was thinking I could just create another
sub to call from the case to decrease repetitveness. The problem is I
use cells from the case row in the calculations. Any ideas on how I
can work this out?

Dim cell As Range
Lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

For Each cell In Range("M1:M" & Lastrow)
Select Case cell.Value

Case 5
Call Calculatecells

Case 5.5
Call Calculatecells

Case 6 7 8 10 etc etc
Call Calculatecells

end select
next


Sub calculatecells

(1500+('cell AB value *10))/ 'cell W value


--
Ramthebuffs
------------------------------------------------------------------------
Ramthebuffs's Profile:
http://www.excelforum.com/member.php...o&userid=16429
View this thread: http://www.excelforum.com/showthread...hreadid=475631



Reply With QuoteReply With Quote