Free Microsoft Excel 2013 Quick Reference

auto changing color

Iam trying to change some linked cell's background color depending on what the linked cell says. Its working on worhsheet_change but i want to know how to do it when i just activate the worksheet. Because when the data changes i want it to automatically do this. Heres what i have.

Private Sub Worksheet_change(ByVal Target As Range)
Dim strcolor As Integer
If Not Intersect(Target, Range("E1:E1000")) Is Nothing Then
        Select Case Target
            Case "work in progress"
                strcolor = 15 'grey
            Case "work completed; no exceptions"
                strcolor = 35 'light green
            Case "work completed; minor recommendations"
                strcolor = 36 'Light yellow
            Case "work completed; with exception needs review"
                strcolor = 38 'Pink
            Case "work completed"
                strcolor = 4 'green
            Case "minor recommendation"
                strcolor = 6  'Yellow
            Case "work completed; with exception"
                strcolor = 3 'Red
            Case Else
        End Select
        Target.Interior.ColorIndex = strcolor
    End If
End Sub

Post your answer or comment

comments powered by Disqus
Help I Want To Change Color Of Cell To Red When The Cell Equal 1 And Blue When The Cell Equal 2 And So On

I am making an extended spreadsheet schedule for a friend, almost done except how to do this easy:

There are 135 (!) cells that will represent day and time in a workweek if someone is available or not.

Easiest procedures we could think of is make the cells some how change color, all red by default (unavailable), change them to green if someone is available.

We were making a button change its color, but it will require a lot of programming (index numbers to remember) is there an easy way to program 135 buttons or cells to change its color on a mouse click?

Please help with code, i tried already so many things my head starts spinning!


I have a macro that changes the color on a row, but depending on which cell I am in different columns arwe changing color. What I want is that if I make a change in row 1 want the color in A1:L1 to change and if i change row 2 i want A2:L2 to change, so alwaws the columns A to L in respective row.

My macro

    Dim intRadnr As Integer 
    Dim intStartrad As Integer 
    Dim intSlutrad As Integer 
    Dim Box As Integer 
    Dim Rad As Integer 
    intRadnr = ActiveCell.Row 
    intStartrad = ActiveSheet.Range("First").Row 
    intSlutrad = ActiveSheet.Range("Last").Row 
    If intRadnr < intStartrad Or intRadnr >= intSlutrad Then 
        MsgBox "Macro can only be executed in rows 21 to 1000" 
        Exit Sub 
    End If 
    ActiveSheet.Unprotect Password:="ssc123" 
    ActiveCell.Range("A1: L1").Select 
    With Selection.Interior 
        .ColorIndex = 19 
        .Pattern = xlSolid 
    End With 
    ActiveCell.Offset(0, 1).Range("A1").Select 
    ActiveCell.Offset(0, 12).Range("A1").Select 
    ActiveCell.FormulaR1C1 = "=TODAY()" 
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ 
    False, Transpose:=False 
    ActiveSheet.Protect Password:="ssc123" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


it is possible to have the series of a stacked graph to change color based on the source value.

i have a stacked cone graph made up of 5 piece (series)
i need each piece colored either green, yellow or red depending on whether its value is large than another


I have a spread sheet with estimated hours and actual hours side by side. i need to have a way so that if the actual hours is greater than the estimated for it to change color ( like red) or somehow distinguish it somehow. any help?

I was hoping someone might be able to help me with some excel questions.
Attached is the xls created to handle the graphic deparment's project schedule.

Basically I'm trying to use a conditional format to

1. If today is 3 days or less from and including the due date (col
i,k,m) , the font color in the row will change color (blue).

2. If a date is entered into col N , the font color in the row will
change color (grey). If the cell does not contain a date it should return to its previous state.

Currently there is some formatting that the department would like to remain active on this sheet.

1. There is an existing conditional format for the cell colors in col
(d,e,f,g,h,j) based on the text entered in the cell (w,r,na)

2. There is a formula for 'comps due' (col i) and 'project route' (col
k) based on the project due date(col m)

Thanks in advance for taking a look at this workbook. -- I look
forward to your feedback!

When I try to paste a chart on top of another, the new series I paste are changing color. I want the series of the copied chart to retain the original color. Is there any way this can be done ?

Have been at this for the last hour; extremely frustrated for a small thing.
Any help will be greatly appreciated. Don't want to manually change color for all series as there are a lot of plots to copy-paste.

Hello guys,

i have a worksheet with a default color of blue-grey. In this worksheet several cells that a have white background.

I would like a simple macro that would enable me double click on any cell (in this sheet ) and have it change color (to say yellow). Double clicking should revert it back to it original color (blue-grey or white -if its one of the cells with a white background).

The second double-click basically 'undo' the action from the first double-click


Hello Mr. Excel people.

I am building some kind of schedule/timetable worksheet for a Satellite Uplink Station. It's based on changes by time slots, frequencies and other parameters.

I would like to change color of cell range if a time range is given. For example:

Cells are divided by time ranges titles: A1=18:55 B1=19:00 C1=19:05...

If I...

Input time value in one cell: A3=19:00
Input time value in another cell: A4=19:15

How could I put in a function that color backgrounds of cells from B2 to E2?

Thanks in advanced!


I am having a slight problem with getting a cell with a date changing to a certain color depending on which date range it lies between.

Date in A1:
May 15 2008

Date Ranges:
A3: Jan 1
A4: Jun 30
If between Jan 1 and Jun 30 the color will be Yellow.

B3: Jul 1
B4: Dec 31
If between Jul 1 and Dec 31 the color will be Green.

I can get the Cell A1 to change color with conditional formatting, but the problem is when a new year comes (2009) the year in A3, A4, B3, and B4 will stay 2008. Is there a way to have the years in the above cells change to display the current year we are in but keep the days specified?

Thank you in advance.

I have a spreadsheet where you can choose one of two options per row.
The listbox says either CASH or CHECK. If the user selects CASH, I would like the row to stay the same color. If the user selects CHECK I would like the row to be highlighted or change color. Upon a glance, anyone who knew the system could then tell how many people paid by CHECK, and who they were.


Hello I hope someone can pint me in the right direction. I am trying to get a cell in one worksheet to change color dependant on the value chosen matching a value in a list that I have created in another worksheet.

For example in worksheet named March08 someone selects 'baskey' from a drop down list in cell A1. I would then want to change the cell color as 'baskey' is in a list named 'Team1' in a worksheet called 'Staff names'

If anyone can help, I would be grateful


If you please, I need help with the vba that would change the cell color of a selected cell simply by clicking on the cell.

As an example:

Cells C7:F7 are currently shaded light green. Cells G7:L7 are shaded light blue. M7 is shaded dark blue. N7:Q7 are shaded light yellow and R7:T7 are shaded light gray.

When any cell in that range (C7:T7) is clicked (selected), I want the cell color to change to it's normal color; like light yellow to yellow, light blue to blue, dark blue to blue, light green to green and finally light gray to gray.

These cells represent headings for a database. When a heading is selected, the database will sort by that column and the header will change colors. I can do the sort code.

If another header is chosen, the previous selection will need to revert back to it's lighter color and the new selection will change as above... so only ONE cell will be changed from it's "normal state" color at a time. This will serve as an indicator as to which column is being used for the sort. To the user, it will have the appearance of switching on and off.

If any other cell on the sheet is selected, then this should not trigger an event change.

I would be most appreciative of any assistance.

I am making a spreadsheet to schedule appointments. What I would like to do is enter data into one cell and have the background color in another change colors.

Example if I put an X in cell B3 then I want the background color in C3 to change colors.

Than you


Using Excel 2002 on XP (pre-SP2)

I have a workbook where certain sheets have had all their cells change color. Both the font and background color have changed (making it unbearable to read). I have a total of 6 sheets in this workbook: 4 have changed colors but the last 2 are normal.

- This is not due to conditional formatting for the cells
- This is not due to my having chosen a Page Setup option of printing in B&W

This happened to me before when I would open a document with 2 open windows. One of the windows always had the weird colors for a given sheet, but the 2nd window had the normal colors. I would simply the weird-colored window and all would be fine. But now this doesn't work anymore. Even if I open 2 windows, save the workbook, and re-open it: both windows will have the bizarre colors.

Any help much appreciated as manually changing all the colors will take me a few hours.


I would like to change color of font when value of cell is over 10000.
Should I do it using IF function? How should I write it? What commands
use to change color of font?

Excel keeps auto-changing my formulas to include other nearby cells. How can
I create and save a formula in a spreadsheet without the program changing my
formula? Each day I have to correct the formula because Excel automatically
changes it. I don't kow how to stop this from happening and I can't find
anything that helps me with this problem.

I have a cell that includes a formula for a particular take rate, if that
take rate is over or below the targeted take rate, I would like the font to
change color.

I'm thinking it is an if statement, for example if goal is 20% and value is
21% change font to bold blue.

I am using Excel 2003. Thanks!

I have a cell I'd like to change color every Monday as an alert to a
task is there a way I can do this using conditional formatting?

Just a little correction, it should be:

otherwise it calculates over 7 month, not 6

"Pieter Kuyck" > wrote in message
> Ken,
> With Conditional Formatting
> Formula Is
> =DATEDIF(A1,TODAY(),"m")>6
> Select a format.. patterns choose a color
> Date in A1 will change of color when the date is more than 6 month ago.
> Pieter
> "kscroggs" > wrote in message
> | I work with Big Brothers Big Sisters and I am trying to
> | schedule our evaluations for 6 months after the original
> | match is made. Is there a formula that will make the
> | dates change color when they go over the 6 month time
> | peroid?
> | I hope that this does not have to be a macro!!!!!!
> | Thank you,
> | Ken Scroggs

I'm wondering if there is a way to format a cell so it changes colors when
the date entered arrives

I have received tremendous help for the following code from Mike ( I’m
waiting for his reply, but I’m in a crunch for time so I thought I would fly

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:G10")) _
Is Nothing Then Exit Sub
Select Case UCase(Target.Value)
Case "ENG 9"
icolor = 3
Case "ENG 10"
icolor = 4
Case "ENG 11"
icolor = 5
Case "ENG 12"
icolor = 6
Case "MATH 9"
icolor = 3
Case "MATH 10"
icolor = 4
Case "MATH 11"
icolor = 5
Case "MATH 12"
icolor = 6
Case "SCI 9"
icolor = 3
Case "SCI 10"
icolor = 4
Case "SCI 11"
icolor = 5
Case "SCI 12"
icolor = 6
Case Else
End Select
Target.Interior.ColorIndex = icolor
End Sub

This code will match any of the “Case”s listed above for cells A1:G10, then
the code throws in the designated color for the background.

If I type “ENG 9” into A1, the cells background will change to red. (which
it should do)

If I link A10 to A1 (same sheet) the code executes perfectly the first
time. When I enter “ENG 10” into A1, A1 background becomes green (which it
should), but A10 stays red with the new text, “ENG 10” in the cell.
I need to get the links to change colors automatically. I can double click
the cell with the link and and the correct color will post up.

Is there a refresh that needs to be done?


I want to change color pallete for different shades. Is there andy way to
change color pallet in office XP.

When I type in a 16 digits (credit card) end 02 it auto change 00. I've
checked the auto word option and the field format and cannot get it to stop
the autochange.

No luck finding an answer? You could always try Google.