How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Aitizaz Your Own Question

Aitizaz
Aitizaz, Software Engineer
Category: Programming
Satisfied Customers: 1289
Experience:  2 Years of experience with VC#,VC++,C,C++
17531195
Type Your Programming Question Here...
Aitizaz is online now
A new question is answered every 9 seconds

I'm having a problem with the conditional formating dates.

Customer Question

I'm having a problem with the conditional formating for two dates. The > and < functions are not working
Submitted: 1 year ago.
Category: Programming
Expert:  Aitizaz replied 1 year ago.

Hello and welcome to Just Answer!i am Aitizaz and i will assist you today

can you share the details of the program please?

Customer: replied 1 year ago.
I have two dates Projected and Actual. I need the actual to be conditionally formatted to be red if it's greater then the projected dated.
Expert:  Aitizaz replied 1 year ago.

which programming language/tool are you using?

Customer: replied 1 year ago.
IEProjected date 10/15/2015 and actual date is 11/15/2015 so i the cell containing 11/15/2015 should be red.
Customer: replied 1 year ago.
excel 2010
Expert:  Aitizaz replied 1 year ago.

Ok! please share the file here and i will do this for you.

Customer: replied 1 year ago.
I don't have access to a phone right now
Expert:  Aitizaz replied 1 year ago.

That is not a problem. Please share the excel file so that i may add this feature.

Customer: replied 1 year ago.
File attached
Expert:  Aitizaz replied 1 year ago.

OK! please give me some time to work on it. i will send a message here when i am done.

Expert:  Aitizaz replied 1 year ago.

which two columns do you want to compare?

Customer: replied 1 year ago.
Are you having any luck?
Expert:  Aitizaz replied 1 year ago.

please let me know about the two columns which you want to compare

Expert:  Aitizaz replied 1 year ago.

Pre-BCAR Meeting and BCAR Mailing?

Customer: replied 1 year ago.
It' not the columns but rows. I need to have the actual row turn Red if isn't greater then the projected row and green if it's less then.Right now not all information is filled out but hopefully it can be applied to what's there.
Expert:  Aitizaz replied 1 year ago.

which two dates would determine that????

Customer: replied 1 year ago.
In column A you will see the two different rows. Projected and Actual
Customer: replied 1 year ago.
do you see those?
Customer: replied 1 year ago.
Do you see those rows?
Expert:  Aitizaz replied 1 year ago.

Yes, i do see them. they are in the B column

Customer: replied 1 year ago.
I need this to be done in all the dates not just column B
Expert:  Aitizaz replied 1 year ago.

you want to compare B12 with B13, C13 with C12 and so on, is this correct?

Expert:  Aitizaz replied 1 year ago.

and you want Row 13 Cells to turn RED if they are higher/later date than the cell in row 12, right?

Customer: replied 1 year ago.
green if it's lower.
Expert:  Aitizaz replied 1 year ago.

OK! please give me some time and i will get back to you with the solution.

Customer: replied 1 year ago.
any luck?
Expert:  Aitizaz replied 1 year ago.

Almost there. please give me a while.

Expert:  Aitizaz replied 1 year ago.

May i write a MACRO for this?

Customer: replied 1 year ago.
I need to apply it alot of sheets.
Can this be used in multiple places?
Expert:  Aitizaz replied 1 year ago.

Yes, you can apply the MACRO across multiple sheets.

Customer: replied 1 year ago.
okay
Expert:  Aitizaz replied 1 year ago.

Alright, get back to you soon!

Customer: replied 1 year ago.
Any luck?
Expert:  Aitizaz replied 1 year ago.

i am writing the MACRO for you, so that you may use that in all the sheets. can you give me an hour so that i may complete the task and test before handing it over to you?

Customer: replied 1 year ago.
sure. I'll follow up back in an hour.
Expert:  Aitizaz replied 1 year ago.

OK

Expert:  Aitizaz replied 1 year ago.

The MACRO is complete and is working. let me know when you are up so that i may tell you how to run it. We need to work on the sheet to add the MACRO and remove FORMATTING of the 13th ROW

Customer: replied 1 year ago.
I'm available now
Customer: replied 1 year ago.
Are you available?
Customer: replied 1 year ago.
Hello? Are you there?
Customer: replied 1 year ago.
Are you able to help me? I really need this answer.
Expert:  Aitizaz replied 1 year ago.

Sorry i was away. Yes i am available.

Expert:  Aitizaz replied 1 year ago.

Send you the solution in a while. please hold on

Expert:  Aitizaz replied 1 year ago.

http://wikisend.com/download/440694/Sample (1).xlsm

Expert:  Aitizaz replied 1 year ago.

Please visit the above link and download the excel sheet

Customer: replied 1 year ago.
got it
Expert:  Aitizaz replied 1 year ago.

One thing is important. When you apply FORMATTING on a specific column of some kind, NOTHING works on that column. So in order for this MACRO to work, you need to REMOVE formatting on the ROW/COLUMN/CELL where you are applying it

Expert:  Aitizaz replied 1 year ago.

to run the MACRO, go to VIEW -> MACRO -> VIEW MACRO and select MACRO1

Customer: replied 1 year ago.
Okay it pulled up the MAcro
Customer: replied 1 year ago.
In VB
Expert:  Aitizaz replied 1 year ago.

Since i have already run the macro on these dates, it will not change the color any further. please change the dates in the ACTUAL column to see affect of this MACRo

Customer: replied 1 year ago.
What If i need it to work on a number of cells? you have it programmed just to do that one cell
Expert:  Aitizaz replied 1 year ago.

Yes, you can change the first argument of the CELLS() function. 13 indicates the ROW number

Customer: replied 1 year ago.
I don't know how to do that
Expert:  Aitizaz replied 1 year ago.

you can ask me anytime if you want to apply the same on any other column

Expert:  Aitizaz replied 1 year ago.

please bookmark my page:

http://www.justanswer.com/computer/expert-aitizaz

you can post question anytime by going to this profile page

Customer: replied 1 year ago.
I need to do it now
Expert:  Aitizaz replied 1 year ago.

OK! please let me know which columns to work on?

Customer: replied 1 year ago.
I need it to work for every date that is in an actual row
Expert:  Aitizaz replied 1 year ago.

OK! you want to do this in this sheet/workbook?

Customer: replied 1 year ago.
I will need to apply to to a number of other worksheets.....
Expert:  Aitizaz replied 1 year ago.

I can tell you where to make changes for other worksheet, if you can identify the rows

Customer: replied 1 year ago.
I need this now. Please start from the beginning and give detailed instructions on how this can be done. I need more information this isn't helpful....
Expert:  Aitizaz replied 1 year ago.

Or if you can allow me, i can do this for you. I can apply the same rule to all the sheets. How many of them are with you ?

Customer: replied 1 year ago.
I don't have them. Please clear the conditional formating and run in on the whole sheet.
Expert:  Aitizaz replied 1 year ago.

OK! i will run it on the whole sheet and will give it to you in a while.

Customer: replied 1 year ago.
do you know how long?
Expert:  Aitizaz replied 1 year ago.

15-20 minutes at most

Customer: replied 1 year ago.
okay thanks
Expert:  Aitizaz replied 1 year ago.

please download the file here:

http://wikisend.com/download/213866/Sample (1).xlsm

Customer: replied 1 year ago.
Okay. I downloaded it but when I change the numbers it still turns green.
Expert:  Aitizaz replied 1 year ago.

when you turn the numbers, you should RUN the macro again

Expert:  Aitizaz replied 1 year ago.

to run the MaCRO, go to VIEW -> MACRO -> VIEW MACRO -> RUN MACRO

Customer: replied 1 year ago.
everytime we chang a number we will have to re run the macro?
Expert:  Aitizaz replied 1 year ago.

Yes, thats true

Customer: replied 1 year ago.
now to apply this to other sheets. How does that work?
Expert:  Aitizaz replied 1 year ago.

Anything else you would like to know?

Expert:  Aitizaz replied 1 year ago.

That needs to be modified according to the ROW numbers of the ACTUAL column

Expert:  Aitizaz replied 1 year ago.

i am going to QUOTE one SET of code here, which works on a single ROW. The rest is a replica of this SET of code with different ROW number

Expert:  Aitizaz replied 1 year ago.

If IsEmpty(Cells(13, iCntr)) Then

ElseIf IsDate(Cells(13, iCntr)) Then
If CDate(Cells(13, iCntr)) > CDate(Cells(12, iCntr)) Then
Cells(13, iCntr).Interior.ColorIndex = 3
ElseIf CDate(Cells(13, iCntr)) < CDate(Cells(12, iCntr)) Then
Cells(13, iCntr).Interior.ColorIndex = 4
End If

End If

Expert:  Aitizaz replied 1 year ago.

Here, in the code snippet above, The function Cells(13,iCntr) indicates 13th ROW and moving columns. By moving columns, i mean the repetition that i have applied at the top (i.e. FOR loop) will traverse all the 25 columns in the 13th ROW

Expert:  Aitizaz replied 1 year ago.

the SAME code has been applied to 17th, 20th, 23rd and all the ROWS containing ACTUAL column

Customer: replied 1 year ago.
Okay so to get this to be applied to a whole worksheet, do I have to chage the code?
Expert:  Aitizaz replied 1 year ago.

for example, in any other sheet, if the ACTUAL column is in the 65th ROW, you need to write

If IsEmpty(Cells(65, iCntr)) Then

ElseIf IsDate(Cells(65, iCntr)) Then
If CDate(Cells(65, iCntr)) > CDate(Cells(64, iCntr)) Then
Cells(65, iCntr).Interior.ColorIndex = 3
ElseIf CDate(Cells(65, iCntr)) < CDate(Cells(64, iCntr)) Then
Cells(65, iCntr).Interior.ColorIndex = 4
End If

End If

Expert:  Aitizaz replied 1 year ago.

Do preserve SPACE after the first line and also NOTICE the CELLS(64,iCntr) to the RIGHT side of the compare operator

Expert:  Aitizaz replied 1 year ago.

64 represents the PROJECTED ROW number

Expert:  Aitizaz replied 1 year ago.

anything else i can do for you?

Customer: replied 1 year ago.
I'm going to try this on all the sheets. If I need help can I reach back out?
Expert:  Aitizaz replied 1 year ago.

Yes!you can send a message anytime you like. Please do rate the answer positive. Thank you very much for coming to just Answer.

Expert:  Aitizaz replied 1 year ago.

Please bookmark this page:

http://www.justanswer.com/computer/expert-aitizaz

you can post question on my profile anytime you have to ask anything.

Expert:  Aitizaz replied 1 year ago.

Thank you!

Expert:  Aitizaz replied 1 year ago.

Hello! i just wanted to know if the solution i provided for other sheets worked for you?