Paleo
03-11-2005, 05:47 PM
Hi guys,
I have built this code, but I am pretty sure it can be improved a lot. Can anyone help me out?
Sub Colorir()
Dim Aulas As Range, Profs As Range
Set Aulas = Range("C4:G68")
' Set Profs = Range("J5:K21") Tried to use this but was unable to manage it working
For Each cell In Aulas
Select Case cell
Case Range("K5")
cell.Interior.ColorIndex = Range("J5").Interior.ColorIndex
Case Range("K6")
cell.Interior.ColorIndex = Range("J6").Interior.ColorIndex
Case Range("K7")
cell.Interior.ColorIndex = Range("J7").Interior.ColorIndex
Case Range("K8")
cell.Interior.ColorIndex = Range("J8").Interior.ColorIndex
Case Range("K9")
cell.Interior.ColorIndex = Range("J9").Interior.ColorIndex
Case Range("K10")
cell.Interior.ColorIndex = Range("J10").Interior.ColorIndex
Case Range("K11")
cell.Interior.ColorIndex = Range("J11").Interior.ColorIndex
Case Range("K12")
cell.Interior.ColorIndex = Range("J12").Interior.ColorIndex
Case Range("K13")
cell.Interior.ColorIndex = Range("J13").Interior.ColorIndex
Case Range("K14")
cell.Interior.ColorIndex = Range("J14").Interior.ColorIndex
Case Range("K15")
cell.Interior.ColorIndex = Range("J15").Interior.ColorIndex
Case Range("K16")
cell.Interior.ColorIndex = Range("J16").Interior.ColorIndex
Case Range("K17")
cell.Interior.ColorIndex = Range("J17").Interior.ColorIndex
Case Range("K18")
cell.Interior.ColorIndex = Range("J18").Interior.ColorIndex
Case Range("K19")
cell.Interior.ColorIndex = Range("J19").Interior.ColorIndex
Case Range("K20")
cell.Interior.ColorIndex = Range("J20").Interior.ColorIndex
Case Range("K21")
cell.Interior.ColorIndex = Range("J21").Interior.ColorIndex
End Select
Next
End Sub
What I need the most is to adapt the select case part to use a loop.
I have built this code, but I am pretty sure it can be improved a lot. Can anyone help me out?
Sub Colorir()
Dim Aulas As Range, Profs As Range
Set Aulas = Range("C4:G68")
' Set Profs = Range("J5:K21") Tried to use this but was unable to manage it working
For Each cell In Aulas
Select Case cell
Case Range("K5")
cell.Interior.ColorIndex = Range("J5").Interior.ColorIndex
Case Range("K6")
cell.Interior.ColorIndex = Range("J6").Interior.ColorIndex
Case Range("K7")
cell.Interior.ColorIndex = Range("J7").Interior.ColorIndex
Case Range("K8")
cell.Interior.ColorIndex = Range("J8").Interior.ColorIndex
Case Range("K9")
cell.Interior.ColorIndex = Range("J9").Interior.ColorIndex
Case Range("K10")
cell.Interior.ColorIndex = Range("J10").Interior.ColorIndex
Case Range("K11")
cell.Interior.ColorIndex = Range("J11").Interior.ColorIndex
Case Range("K12")
cell.Interior.ColorIndex = Range("J12").Interior.ColorIndex
Case Range("K13")
cell.Interior.ColorIndex = Range("J13").Interior.ColorIndex
Case Range("K14")
cell.Interior.ColorIndex = Range("J14").Interior.ColorIndex
Case Range("K15")
cell.Interior.ColorIndex = Range("J15").Interior.ColorIndex
Case Range("K16")
cell.Interior.ColorIndex = Range("J16").Interior.ColorIndex
Case Range("K17")
cell.Interior.ColorIndex = Range("J17").Interior.ColorIndex
Case Range("K18")
cell.Interior.ColorIndex = Range("J18").Interior.ColorIndex
Case Range("K19")
cell.Interior.ColorIndex = Range("J19").Interior.ColorIndex
Case Range("K20")
cell.Interior.ColorIndex = Range("J20").Interior.ColorIndex
Case Range("K21")
cell.Interior.ColorIndex = Range("J21").Interior.ColorIndex
End Select
Next
End Sub
What I need the most is to adapt the select case part to use a loop.