Hello and welcome to Just Answer!i am Aitizaz and i will assist you today
can you share the details of the program please?
which programming language/tool are you using?
Ok! please share the file here and i will do this for you.
That is not a problem. Please share the excel file so that i may add this feature.
OK! please give me some time to work on it. i will send a message here when i am done.
which two columns do you want to compare?
please let me know about the two columns which you want to compare
Pre-BCAR Meeting and BCAR Mailing?
which two dates would determine that????
Yes, i do see them. they are in the B column
you want to compare B12 with B13, C13 with C12 and so on, is this correct?
and you want Row 13 Cells to turn RED if they are higher/later date than the cell in row 12, right?
OK! please give me some time and i will get back to you with the solution.
Almost there. please give me a while.
May i write a MACRO for this?
Yes, you can apply the MACRO across multiple sheets.
Alright, get back to you soon!
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?
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
Sorry i was away. Yes i am available.
Send you the solution in a while. please hold on
Please visit the above link and download the excel sheet
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
to run the MACRO, go to VIEW -> MACRO -> VIEW MACRO and select MACRO1
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
Yes, you can change the first argument of the CELLS() function. 13 indicates the ROW number
you can ask me anytime if you want to apply the same on any other column
please bookmark my page:
you can post question anytime by going to this profile page
OK! please let me know which columns to work on?
OK! you want to do this in this sheet/workbook?
I can tell you where to make changes for other worksheet, if you can identify the rows
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 ?
OK! i will run it on the whole sheet and will give it to you in a while.
15-20 minutes at most
please download the file here:
when you turn the numbers, you should RUN the macro again
to run the MaCRO, go to VIEW -> MACRO -> VIEW MACRO -> RUN MACRO
Yes, thats true
Anything else you would like to know?
That needs to be modified according to the ROW numbers of the ACTUAL column
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
If IsEmpty(Cells(13, iCntr)) ThenElseIf 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 IfEnd If
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
the SAME code has been applied to 17th, 20th, 23rd and all the ROWS containing ACTUAL column
for example, in any other sheet, if the ACTUAL column is in the 65th ROW, you need to write
If IsEmpty(Cells(65, iCntr)) ThenElseIf 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 IfEnd If
Do preserve SPACE after the first line and also NOTICE the CELLS(64,iCntr) to the RIGHT side of the compare operator
64 represents the PROJECTED ROW number
anything else i can do for you?
Yes!you can send a message anytime you like. Please do rate the answer positive. Thank you very much for coming to just Answer.
Please bookmark this page:
you can post question on my profile anytime you have to ask anything.
Hello! i just wanted to know if the solution i provided for other sheets worked for you?