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

Topic

New Reply

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!

Reply | Quote

Replies

You can change a cell color like this:

    Activesheet.Range("A1").Interior.Colorindex = 5

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.

Reply | Quote

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.

Reply | Quote

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:

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

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.

Reply | Quote

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

Reply | Quote

OK, try the attached workbook.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote