PDA

View Full Version : Solved: VBA Code Help please :)



curious_24
09-11-2012, 08:59 PM
Hi,
I'm fairly new to VBA Code and so have been reading alot but feel a bit all over the place atm - so any direction to "useful" links will be much appreciated. Anyways I'm trying to create a macro button that will compare a list of dates to the current date and than change the font of dates that have passed to red. I've created a macro button that will change a selection of cells font to red, just need to work on the other details of comparing the dates and selecting the past dates only (complicated part) lol...Any help much appreciated.

jolivanes
09-11-2012, 10:39 PM
Try this. Dates to compare to today are in Column A and start at A2.
Change the Row and Column references as required.


Sub CompareToToday()
Dim Today
Dim c As Range
Today = Now
For Each c In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
If c.Value < Today Then c.Interior.Color = vbRed
Next c
End Sub

Bob Phillips
09-12-2012, 12:12 AM
Why not just use conditional formatting with a formula of

=A2<TODAY()

and a format of red. That way it automatically keeps itself refreshed.

curious_24
09-12-2012, 02:19 PM
@jolivanes - thanks it runs almost perfect except that it highlights the spaces in between as well (also I've just added -1 to Today, so that it doesn't highlight today's date as well as I only want the past dates) :) Do you have any suggestions? appreciate the help.
@xld - tried that as well and it highlights the wrong cell lol, but I guess I need to play around with it more if the above doesn't work out. thanks heaps though :)

jolivanes
09-12-2012, 03:40 PM
Replace the word "Interior" with "Font" (without quotations)

curious_24
09-12-2012, 03:57 PM
thanks again for your help I've resolved the problem:
I just added:
And c.Value <> "" Then c.Font.Color = vbRed

Your awesome jolivane :)
thanks again.

jolivanes
09-12-2012, 10:20 PM
Thank you for the feed back

Bob Phillips
09-13-2012, 01:36 AM
@xld - tried that as well and it highlights the wrong cell lol, but I guess I need to play around with it more if the above doesn't work out. thanks heaps though :)

Then you must be referencing the wrong cell.

It is far better to use CF than adding VBA.

curious_24
09-13-2012, 02:33 PM
@xld - I guess if I could get it working with CF, I've got the column A with the dates and some cells are blank and some cells have text in the Column A. I've selected CF and new rule, than chose "use a formula to determine which cells to format" and entered in the formula you gave me except for changing A2 to A8 because that's where the dates start but it highlights some other cell other than A8 and doesn't highlight all of the past dates.

Bob Phillips
09-13-2012, 04:09 PM
If you put =A8<TODAY() you must ensure that the first cell that you are applying the CF to is in row 8.

curious_24
09-13-2012, 06:29 PM
@xld, yes I know that. It's still not working and if I enter in A8, and put a date into A7 - it will highlight the date in A7 only and the date in A7 is a future date not a past date, hence I think the formula may be wrong.

curious_24
09-13-2012, 07:12 PM
@xld - you are absolutely right - CF is definitely better and easier and automatically applies when your typing the date in - thanks for your help and time.......I've managed to solve the formula problem by following some of the instructions in the link: http://www.techonthenet.com/excel/questions/cond_format4.php

curious_24
09-13-2012, 07:16 PM
For quick reference: what I did is
*highlight the whole column A
*did a CF =ISBLANK(A1)=TRUE, meaning to ignore any blank space
*did another CF using the "Format only cells with" and than selected "CELL VALUE" in the first drop down and than "less than" in the second drop down and than typed in "=NOW()-1"in the last drop down which calculates yesterdays date.
*and of course formated the font to change to red
*thanks for your very helpful suggestion, it works perfectly.

Bob Phillips
09-14-2012, 12:34 AM
Glad you persevered with it and it worked well.

I would just suggest using =TODAY()-1 rather than =NOW()-1, as NOW includes the hours, so it could change during the same day.

curious_24
09-16-2012, 02:49 PM
Thanks again, will do that.