• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code to highlight cell based on certain text or date

P

PSG

Member
Hello All,
Attached file has sample data in sheet 1 and desired output in another sheet.
I need to color cell based on certain conditions;
  1. If date in the row is less than or equal to today, It should change color of all the cell prior to it as Green. Example Cell O3 has today's date. All cells in row 3 has been turned to Green.
  2. If any cell has TBD in it, All cell following it in the row should turn to PInk. Example Cell M5 has "TBD". All cells in Row 5 after TBD has turned to Pink.
  3. If in any row we have NA it should turn to Green, but only up to the point where it finds a future date. Example Row 6. Till cell O6 we either have NA or date less than TODAY, hence everything is in Green. But Cell P6 has date Greater than Today, so from that point onward we have cells in Pink.
Let me know if you need more information.
 

Attachments

  • Pipeline tracking.xlsx
    10.9 KB · Views: 7
I'd suggest you can solve this without using VBA
Try Conditional Formatting
 
Hello Hui,

I tried doing that, but the desired output is not coming. Hence posted onto the forum. Please help!
 
Hi ,

Row 2 colouring does not follow the logic that you have described ; even the date in F2 is greater than today's date ; why are only the cells beyond Q2 coloured pink ?

Narayan
 
Hi Narayan,

First and foremost, Thanks for looking into the problem. Now the back to your concerns.

Row 2 is following the logic, If you see the dates in F2, G2 and K2 are from 2013 followed by NA until cell R2 where a future date coming. Hence till that point all cells are green and Pink afterward.

I hope, I am not confusing members and Excel Ninjas here on the forum!!
 
Hi ,

Sorry , my mistake ; I didn't see the date in the cell ; I assumed that since you have formatted all the dates to show only the day and the month , they are all of this year ; I think the correct way to format dates which span more than 1 year is as mm/yy at the least. Surely the day cannot be more significant than the year ?

Narayan
 
You must log in or register to reply here.
Back
Top