Forum Discussion

enghietan1986's avatar
enghietan1986
Occasional Reader
Jul 07, 2026

how to ignore conditional formatting for a cell that is not empty but invisible (cell format ;;;)

hi,

i need help with a conditional formatting of cells that are not empty but invisible (cell format to ;;; (invisible)). I have a calendar where the dates are invisible (outside of the month) but the cell formatting applies to it (cuz it is that date, i.e., public holiday). i have attached the screenshots 

 and i hope someone can help me out. thank you so much.

 

regards,

angel

 

 

excel

1 Reply

  • m_tarler's avatar
    m_tarler
    Silver Contributor
    Jul 08, 2026

    You just need to add another condition to that conditonal formatting rule to exclude dates <> that month.

    Also, I might be wrong but it appears your highlights are a day off because the top left corner of your 'Applied To' range doesn't match your formula (B5 vs A5).  Try this formula:
    =(MATCH(B5, SCHEDULE!$B:$B, 0)>0)*(B5>=DATEVALUE($A5 &" 1, "&$A$2))*(B5<=EOMONTH(DATEVALUE($A5 &" 1, "&$A$2),0))