Conditional formatting of Dates - highlighting of columns

dbirdy · Jul 11th 2006

Well I spent some time yesterday trying to figure this one out. It was only last week that I have taught myself how to use VBA in excel. However this looks like a though one.

Simply what i would like to do is highlight a column of a certain date (till a certain row number) based on a list of dates.

eg. Across the top I have a set of dates and on another sheet i have a list of dates.

I understand that there is a VBA code for such conditional formatting but i can't get my head around it.

Refer to the following excel spreadsheet as an example....

Thanks for your help ahead of time.

jmhans · Jul 11th 2006

Re: Conditional formatting of Dates - highlighting of columns

Here's a start:

Code

Private Sub Worksheet_Calculate()
Dim myDate As Date


'////////////////////////////////////////////////////////////////
'Replace this with code that determines which column to highlight
'(other sheets didn't contain any data
myDate = CDate("7/12/06")
'///////////////////////////////////////////////////////////////


Cells.Interior.ColorIndex = Null
Columns(WorksheetFunction.Match(CLng(myDate), Rows(1), False)).Interior.ColorIndex = 6


End Sub

Display More

Place that in the appropriate Worksheet code module. It will fire any time formulas on the worksheet are calculated. See commented code and modify to suit your needs. Hope this helps!

dbirdy · Jul 11th 2006

Re: Conditional formatting of Dates - highlighting of columns

Thanks for the reply but it does not look like it is working for me.

This is the VBA code i have:

Code

Private Sub Worksheet_Calculate()
    Dim myDate As Date
     
    myDate = CDate("Q2:Q6")
   
    Cells.Interior.ColorIndex = Null
    Columns(WorksheetFunction.Match(CLng(myDate), Rows(1), False)).Interior.ColorIndex = 6


End Sub

and attached is the worksheet

dbirdy · Jul 11th 2006

Re: Conditional formatting of Dates - highlighting of columns

Could anyone else help me out here please?

StefanG · Jul 11th 2006

Re: Conditional formatting of Dates - highlighting of columns

Hi dbirdy,
PMFJI
This is the best i can do, building on jmhans's code. I'm not sure whether you really like to use it in the Worksheet_Calculate, and hence get this recalculated "all the time"....

Code

Dim MyDate, I
    Cells.Interior.ColorIndex = Null
    For I = 2 To 6
        MyDate = Range("Q" & I)
        Columns(WorksheetFunction.Match(CLng(MyDate), Rows(1), False)).Interior.ColorIndex = 6
    Next I

Stefan

dbirdy · Jul 11th 2006

Re: Conditional formatting of Dates - highlighting of columns

So far i have just copied StefanG's code into sheet1 VBA sheet.

It doesn't seem to work when i enter a date in the row A1:K1. It's supposed to highlight any date i have listed in column Qq2:Q6

What am i missing?

Dave Hawley · Jul 11th 2006

Re: Conditional formatting of Dates - highlighting of columns

Why not simply use Conditional Formatting?

Where the Formula Is: =COUNTIF(QQ2:Q6,A1)

dbirdy · Jul 11th 2006

Re: Conditional formatting of Dates - highlighting of columns

Yeah i have tried condiotional formatting but is not engineered to the way i like that. My example is a simplified version of what i am after.

Dave Hawley · Jul 11th 2006

Re: Conditional formatting of Dates - highlighting of columns

It will work. Show us a small representive example.

dbirdy · Jul 11th 2006

Re: Conditional formatting of Dates - highlighting of columns

Please see the attached worksheet i have posted previously.

In the excel file you will see the dates i wish to have shadded a colour accross the top. On the right is the criteria of dates for shading.

When a date applies i want the shading to extend from the respective date down to row 10 or so.

Thanks.

Dave Hawley · Jul 11th 2006

Re: Conditional formatting of Dates - highlighting of columns

Select A1:K1 and the use formula below in CF.

=COUNTIF($Q$2:$Q$6,A$1)

dbirdy · Jul 12th 2006

Re: Conditional formatting of Dates - highlighting of columns

Yep that function works fine. However there is one problem, i have condtional formatting and other formulas in lower cells already working and was looking at a VBA code to get around it.

I also understand that Condtional formatting takes precendce and i am happy with that.

So apart from the simple Conditional Formatting method, can i use VBA to do what i have previously asked?

Dave Hawley · Jul 12th 2006

Re: Conditional formatting of Dates - highlighting of columns

See: Get Around Excel's 3 Criteria Limit in Conditional Formatting which shows how to use the Worksheet_Change Event for this.

dbirdy · Jul 12th 2006

Re: Conditional formatting of Dates - highlighting of columns

Sorry, but i can't get my head around it...

a little more help please....

Dave Hawley · Jul 12th 2006

Re: Conditional formatting of Dates - highlighting of columns

Based soley on your example in your 1st post. Right click on the sheet name tab, choose "View Code" and in here paste the code below

Code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rDates As Range
Dim rComDates As Range
Dim rCell As Range


Set rDates = Range("A1", Range("IV1").End(xlToLeft))
Set rComDates = Range("Q1", Range("Q65536").End(xlUp))


    For Each rCell In rDates
        If WorksheetFunction.CountIf(rComDates, rCell) > 0 Then
            rCell.Interior.ColorIndex = 6
        End If
    Next rCell




End Sub

Display More

This code will fire whenever any data is added to the Worksheet.

dbirdy · Jul 13th 2006

Re: Conditional formatting of Dates - highlighting of columns

Yep that works fantastic.

Ok one more for you.

What is the code to make it colour from the coloured date cell down to say row 10?

Dave Hawley · Jul 13th 2006

Re: Conditional formatting of Dates - highlighting of columns

Change: rCell.Interior.ColorIndex = 6 to rCell.Range("A1:A10")Interior.ColorIndex = 6

dbirdy · Jul 13th 2006

Re: Conditional formatting of Dates - highlighting of columns

Thanks very much for your help mate.

You just left a decimal out:

Hence: rCell.Range("A1:A10").Interior.ColorIndex = 6

Have a good one.

dbirdy · Jul 18th 2006

Re: Conditional formatting of Dates - highlighting of columns

Ok, i have another question for you all.

I have the values to be highlighted on another sheet (Sheet 2).

I can't get the code to refer to another sheet, it always come up with an error. I would like it to refer to sheet 2.

This is the line i am have trouble with:

Set rComDates = Range(Sheets("sheet2")).Cells("Q1"),Range(Sheets("sheet2")).Cells("Q20").End(xlUp))

Thanks

Dave Hawley · Jul 18th 2006

Re: Conditional formatting of Dates - highlighting of columns

Use

Code

With Sheet2
        Set rComDates = .Range(.Range("Q1"), .Range("Q20").End(xlUp))
    End With

Please use code tags for code in future.

Conditional formatting of Dates - highlighting of columns

Participate now!

Share