?
Solved

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

Posted on 2011-04-21
5
Medium Priority
?
352 Views
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?

Gary
0
Comment
Question by:garyrobbins
  • 2
  • 2
5 Comments
 
LVL 11

Expert Comment

by:Runrigger
ID: 35441956
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.
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 35443203
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
Mon
Tue
Wed
Thur
Fri

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?

Sid

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
    Next
End Sub

Open in new window

Color-Sheet-Tabs.xls
Color-Sheet-Tabs.jpg
0
 

Author Comment

by:garyrobbins
ID: 35462946
SiddharthRout,
Thank you.  You make this sound easy...  Could you help me out with a brief explanation of how this code works?

Gary
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35462961
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?

Sid
0
 

Author Closing Comment

by:garyrobbins
ID: 35747258
Great solution.  Now I need to get better at understanding VBA.

Gary
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

850 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