Consulting

Results 1 to 8 of 8

Thread: Easy code

  1. 03-11-2005, 05:47 PM #1
    Paleo
    Paleo is offline
    Administrator
    VP-Knowledge Base     VBAX Master
    Joined
    Jan 2005
    Location
    Porto Alegre - RS - Brasil
    Posts
    1,219
    Location

    Easy code

    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.
    Best Regards,

    Carlos Paleo.

    To every problem there is a solution, even if I dont know it, so this posting is provided "AS IS" with no warranties.

    If Debugging is harder than writing a program and your code is as good as you can possibly make
    it, then by definition you're not smart enough to debug it.




    http://www.mugrs.org
    Reply With Quote Reply With Quote
  2. 03-11-2005, 06:01 PM #2
    Jacob Hilderbrand
    Jacob Hilderbrand is offline
    Site Admin
    Jedi Master     VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    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[



    Reply With Quote Reply With Quote
  3. 03-11-2005, 06:10 PM #3
    Paleo
    Paleo is offline
    Administrator
    VP-Knowledge Base VBAX Master
    Joined
    Jan 2005
    Location
    Porto Alegre - RS - Brasil
    Posts
    1,219
    Location
    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.
    Best Regards,

    Carlos Paleo.

    To every problem there is a solution, even if I dont know it, so this posting is provided "AS IS" with no warranties.

    If Debugging is harder than writing a program and your code is as good as you can possibly make
    it, then by definition you're not smart enough to debug it.




    http://www.mugrs.org
    Reply With Quote Reply With Quote
  4. 03-11-2005, 06:35 PM #4
    Jacob Hilderbrand
    Jacob Hilderbrand is offline
    Site Admin
    Jedi Master     VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    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



    Reply With Quote Reply With Quote
  5. 03-11-2005, 07:10 PM #5
    Paleo
    Paleo is offline
    Administrator
    VP-Knowledge Base VBAX Master
    Joined
    Jan 2005
    Location
    Porto Alegre - RS - Brasil
    Posts
    1,219
    Location
    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.
    Best Regards,

    Carlos Paleo.

    To every problem there is a solution, even if I dont know it, so this posting is provided "AS IS" with no warranties.

    If Debugging is harder than writing a program and your code is as good as you can possibly make
    it, then by definition you're not smart enough to debug it.




    http://www.mugrs.org
    Reply With Quote Reply With Quote
  6. 03-11-2005, 07:34 PM #6
    Jacob Hilderbrand
    Jacob Hilderbrand is offline
    Site Admin
    Jedi Master     VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Not wanting to declare variables is no excuse for not using Option Explicit.

    Dim cell As Range



    Reply With Quote Reply With Quote
  7. 03-12-2005, 04:56 AM #7
    XL-Dennis
    XL-Dennis is offline
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    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.
    Kind regards,
    Dennis
    ExcelKB | .NET & Excel | 2nd edition PED

    Reply With Quote Reply With Quote
  8. 03-12-2005, 08:05 PM #8
    Paleo
    Paleo is offline
    Administrator
    VP-Knowledge Base VBAX Master
    Joined
    Jan 2005
    Location
    Porto Alegre - RS - Brasil
    Posts
    1,219
    Location
    Hi Jake,

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

    Carlos Paleo.

    To every problem there is a solution, even if I dont know it, so this posting is provided "AS IS" with no warranties.

    If Debugging is harder than writing a program and your code is as good as you can possibly make
    it, then by definition you're not smart enough to debug it.




    http://www.mugrs.org
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules