Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Oct 2001
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing Cell colours in Excel using VBA (Excel '97)

    Anyone know how to do the above in excel? I would be able to do it with conditional formatting but i need 5 conditions, MS only gave us three!

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Cell colours in Excel using VBA (Excel '97)

    You can change a cell color like this:

    <pre> Activesheet.Range("A1").Interior.Colorindex = 5
    </pre>

    The above will change the color of cell A1 on the active sheet to Blue.

    The above will work in a Subroutine. However, from the sound of this message and your message in the Excel forum, it sounds like you want to do this from a User Defined Function. The above will not work from a User Defined Function because a User Defined Function can only return values to the cell that executed it, it can not make other changes to a worksheet.

    To do what I think you want to do, you are probably going to have to use the worksheet change event routine, check your conditions there, and change the color. I you can be a bit more specific about what you want to do, we can help with the code.
    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Oct 2001
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Cell colours in Excel using VBA (Excel '97)

    Sorry, I'm new to this!
    If there is a date in say cell A1, i would have the =WEEKDAY(A1,2) formula in cell B1 to tell me what day the date lands on, i then want to put another formula into cell C1 to change the colour of cell A1 depending on the day in Cell B1. I could do it with conditional formatting if they gave me 5 arguments (i need a different colour for each day of the working week) but as usual MS have come up short. Hope anyone can help, cheers Jamie.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Cell colours in Excel using VBA (Excel '97)

    As I stated earlier, there is no way for a User Defined function to modify another cell, It can only return a value to the cell that called it. So, there is no way to do what you want the way that you described it.

    However, the following code, placed in the Worksheet Change event routine for the sheet in question should do what you are trying to do:

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range, oRange As Range
    Set oRange = Intersect(Target, Range("A1:A10"))
    If oRange Is Nothing Then
    Exit Sub
    End If
    For Each oCell In oRange
    Select Case Weekday(oCell.Value, vbMonday)
    Case 2
    oCell.Interior.ColorIndex = 7
    Case 3
    oCell.Interior.ColorIndex = 6
    Case 4
    oCell.Interior.ColorIndex = 5
    Case 5
    oCell.Interior.ColorIndex = 4
    Case 6
    oCell.Interior.ColorIndex = 3
    Case Else
    oCell.Interior.ColorIndex = xlColorIndexAutomatic
    End Select
    Next oCell
    End Sub
    </pre>


    Since you weren't very specific, this code assumes that the dates in question are in the range A1:A10. You would need to modify that to fit your needs. You will probably also want to change the colors that this sets.
    Legare Coleman

  5. #5
    New Lounger
    Join Date
    Oct 2001
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Cell colours in Excel using VBA (Excel '97)

    I have attached the file i need the above to happen on. I can't explaint it very well. I want the cell with the date in to change colour according to the day that date lands on! Please help. I've tried the code you told me and it says compiled error. Since i have NO experiance using VBA i don't know what is wrong. thanks for trying to help but could you PLEASE try again?

    thanks
    Jamie
    Attached Files Attached Files

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Cell colours in Excel using VBA (Excel '97)

    OK, try the attached workbook.
    Attached Files Attached Files
    Legare Coleman

  7. #7
    New Lounger
    Join Date
    Oct 2001
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Cell colours in Excel using VBA (Excel '97)

    Thats great, but how did you do it? If it takes too long to explain a rough idea would help, the thing is this file is a week old now and there is a up-to-date one at work that i need to apply whatever you done to the work sheet to.
    Thanks
    Jamie

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Cell colours in Excel using VBA (Excel '97)

    Open the workbook and go to the VBA Editor. In the Project Explorer at the left of the screen double click on the Sheet1 object. In the left drop down list above the edit area select Worksheet. In the right had drop down list select Change. The code should now be displayed in the edit area.
    Legare Coleman

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Changing Cell colours in Excel using VBA (Excel '97)

    Jamie,

    If you have little experience using the VB editor and object browser, you can right click on the sheet tab for Sheet1 (or whatever sheet has the code) and select View Code. You could then just copy all the code you see and paste into another sheet (right click - view code) if that is what you require.

    Andrew C

  10. #10
    New Lounger
    Join Date
    Oct 2001
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Cell colours in Excel using VBA (Excel '97)

    Cheers Guys, It worked a treat, saves me lots of time, and you'll be glad to know i have decided to buy a rather large book on VBA, any ideas on which one is the best???

    Jamie

Posting Permissions

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