How do I format a background color with a cell formula?

Posted on 2011-04-21
Last Modified: 2012-05-11
I have a cell (A1) that displays the “Tab Name”.  The tab name is a date (for each day of the month), eg. 11-04-18.  

Each day of the week has a different color associated with it:
Mon – Yellow
Tue – Blue
Wed – Pink
Thur – Gold
Fri – Green

I want to program this cell with a formula to change background color based on the day of the week for that date.  Eg. 11-04-08 is background color Yellow; 11-04-19 is Blue.

First, can I do this with a cell formula, and then where do I start?

Would it be easier to program a background color for the actual tab?

What would you recommend?

Question by:garyrobbins
    LVL 11

    Expert Comment

    A formula can not change the background colour of a cell.

    You can use conditional formatting, however, as you are using 2003, you can only enter three conditions to change the background colour, not helpful when you need 5!

    You will need to create a macro based on cell update for cell A1, when you enter the date, it will change the background colour based on the "Day" extracted from the date.
    LVL 30

    Accepted Solution

    If you mean SheetTabs by "Tab Name" then try this small Macro. Please paste it in a module.

    I am assuming that in Sheet "Sheet1" you have the following data starting form cell A1 and down
    COL A

    and in Col B

    You have the colors instead of just names. Please see ScreenShot.

    Also Attached is a sample file for you to try.

    Hope this is what you wanted?


    Code Used

    Sub Sample()
        Dim ws As Worksheet
        Dim WDay As String
        Dim myArray() As String
        For Each ws In ActiveWorkbook.Sheets
            If ws.Name <> "Sheet1" Then
                myArray = Split(ws.Name, "-")
                WDay = WeekdayName(Weekday(DateSerial(myArray(0), myArray(1), myArray(2))), True)
                For i = 1 To 5
                    If UCase(Trim(Sheets("Sheet1").Range("A" & i).Value)) = UCase(Trim(WDay)) Then
                        ws.Tab.Color = Sheets("Sheet1").Range("B" & i).Interior.Color
                        Exit For
                    End If
                Next i
            End If
    End Sub

    Open in new window


    Author Comment

    Thank you.  You make this sound easy...  Could you help me out with a brief explanation of how this code works?

    LVL 30

    Expert Comment

    Sure Gary.

    First download the workbook which I have attached in the above post and then run the macro Sample. Does it do what you want?


    Author Closing Comment

    Great solution.  Now I need to get better at understanding VBA.


    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now