Conditional Formatting based on month name
I have a sheet where C2 contains the typed text of a month, i.e. February.
Range C4:D14 contains actual dates spanning several months. I would like to highlight the cells that contain the month in C2. I've floundered around for several hours without a solution. Any help? P.S. I need C2 to remain text so it won't change until I change it. -- David |
Try:
Select C4:D14 Click Format Conditional Formatting Under Condition 1, make the settings as: Formula Is| =AND(TEXT(C4,"mmmm")=$C$2,C4<"") Click Format button Patterns tab Light brown? OK Click OK at the main dialog -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "David" wrote in message ... I have a sheet where C2 contains the typed text of a month, i.e. February. Range C4:D14 contains actual dates spanning several months. I would like to highlight the cells that contain the month in C2. I've floundered around for several hours without a solution. Any help? P.S. I need C2 to remain text so it won't change until I change it. -- David |
Max wrote
Formula Is| =AND(TEXT(C4,"mmmm")=$C$2,C4<"") Excellent!!! Reducing to =TEXT(C4,"mmmm")=$C$2 works as well, although I can see why the test for blanks might come in handy someday. Thanks. P.S. While waiting for a response I was able to type 2/1 (that makes changing months easier) in C2 and custom format as mmmm and achieve what I wanted with: Formula is|=MONTH(C4)=MONTH($C$2) The secret was making C2 absolute, which I had overlooked in previous trials. -- David |
You're welcome !
Formula Is| =AND(TEXT(C4,"mmmm")=$C$2,C4<"") The ... C4<"" condition is included to prevent any blank cells within the source range from "wrongly" triggering the CF when C2 = January -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "David" wrote in message ... Max wrote Formula Is| =AND(TEXT(C4,"mmmm")=$C$2,C4<"") Excellent!!! Reducing to =TEXT(C4,"mmmm")=$C$2 works as well, although I can see why the test for blanks might come in handy someday. Thanks. P.S. While waiting for a response I was able to type 2/1 (that makes changing months easier) in C2 and custom format as mmmm and achieve what I wanted with: Formula is|=MONTH(C4)=MONTH($C$2) The secret was making C2 absolute, which I had overlooked in previous trials. -- David |
Max wrote
The ... C4<"" condition is included to prevent any blank cells within the source range from "wrongly" triggering the CF when C2 = January So I discovered when testing for January. Thanks again. -- David |
All times are GMT +1. The time now is 10:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com