Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

(apologies if this question is difficult to understand, i'm unable to post an image)

I've tried finding a solution for this online but haven't had much luck.

Ideally, I want to use conditional formatting to highlight each cell within a row of dates between two dates. There one problem.. I want the colour to be specified by a value based in another cell.. if this can be done using a module then great!

i.e. I've inserted a each day within the calendar horizontally across the spreadsheet from F2 for an entire year. IF C2 = 01/12/14 & D2 = 24/12/14 and E2 = 3. All cells between dates of C2 and D2 will fill with the colour RED.. likewise with IF C3 = 01/05/14 & D3 = 05/05/14 and E3 = 2, the cells will be filled with the colour of blue.


share|improve this question
up vote 1 down vote accepted

I don't believe there is any method to dynamically choose the color when using Excel's built in Conditional Formatting. You could, as you guessed, use some VBA to accomplish though.

Assuming Column C has the lower date in the range range, Column D has the upper date in the range, Column E contains a colorindex (see here), and Column F has the date we are testing (and this is the cell we are coloring) you can create the following subroutine:

Public Sub update_colors()
    Dim rngDates As Range
    Set rngDates = Sheet1.Range("F2:F3")

    Dim rngDateCell As Range
    For Each rngDateCell In rngDates.Cells
        If Sheet1.Cells(rngDateCell.Row, 3).Value <= rngDateCell.Value And Sheet1.Cells(rngDateCell.Row, 4).Value >= rngDateCell.Value Then
            rngDateCell.Interior.ColorIndex = Sheet1.Cells(rngDateCell.Row, 5).Value
        End If
    Next rngDateCell

End Sub

Set F2:F3 in the third line to the entire range of dates in your worksheet.

You can kick off this subrouting/udf with a button. On your developer tab (go here for instructions on how to enable it, if you haven't already) Click "Insert" and choose the button under "Form Controls". Draw the button somewhere on the sheet (you can move it afterwards), and choose the subroutine you just created from the list. Now when you click the button, the subroutine will run.

If you don't like the colors, you can use something besides .interior.colorindex() to set the cell color. I'm a big fan .Interior.Color = RGB(,,) You would need to have the RGB values in the sheet somewhere to pull that off, but you will have millions of colors to choose from instead of just the 56 that colorindex allows.

share|improve this answer
that's exactly what I needed! thanks :) – damm Jan 30 '14 at 9:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .