PDA

View Full Version : [SOLVED] Easy code



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.

Jacob Hilderbrand
03-11-2005, 06:01 PM
Instead of the Select Case how about this.


If cell.Column = 11 And cell.Row > 4 And cell.Row < 21 Then
cell.Interior.ColorIndex = cell.OffSet(0,-1).Interior.ColorIndex
End If[

Paleo
03-11-2005, 06:10 PM
Hi Jake,

it didnt work.

I need to check if these cells value


Set Aulas = Range("C4:G68")


is equal to this cells value


Set Profs = Range("K5:K21")


so, if it was I will set the first group of cells interior color equals to the secound group of cells interior color.

Jacob Hilderbrand
03-11-2005, 06:35 PM
Ok, how about this.


Option Explicit

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
For i = 5 To 21
If cell.Value = Range("K" & i).Value Then
cell.Interior.ColorIndex = Range("J" & i).Interior.ColorIndex
Exit For
End If
Next i
Next
End Sub

Paleo
03-11-2005, 07:10 PM
Hi Jake,

great this time it worked out fine! Thanks!

Just cant use the Option Explicit because it would generate an error for the cell variable.

Jacob Hilderbrand
03-11-2005, 07:34 PM
Not wanting to declare variables is no excuse for not using Option Explicit.


Dim cell As Range

XL-Dennis
03-12-2005, 04:56 AM
Just cant use the Option Explicit because it would generate an error for the cell variable.


Eh? Good coding practice state we should *always* use it as Jake also points out.

Paleo
03-12-2005, 08:05 PM
Hi Jake,

yes, as usual you right. I will declare it! Thanks!