oheavingham
12-08-2020, 09:04 AM
Hey everyone, thank you for looking into this.
I am a bit stuck or can't think at the moment. My goal/project is to create a working holiday spreadsheet, that highlights available cells and non-available cells using the Conditional Formatting tool.
In each cell will have the initials of the person and is broken up with the "/" symbol if there is more one person in that cell. My vision is to highlight the blank cells one colour (i.e. Green), then the cells with the symbol "/" another colour (i.e. Red), but for those cells that only have one person to be highlight another colour. The issue I am having is in the range of cells I am working with, I have included Saturdays and Sundays, which I want the formula to avoid. I can not figure this out and I have tried a variety of different formulas such as, ISTEXT. Wondering if somebody could help me with the line #3?
Dim AvaRng As Range
Set AvaRng = Range("C3:N33")
AvaRng.FormatConditions.Delete
'#1
AvaRng.FormatConditions.Add xlExpression, Formula1:="=ISBLANK(C3:N33)"
'#2
AvaRng.FormatConditions.Add xlExpression, Formula1:="=SEARCH(""/"",C3:N33)"
'#3 -
AvaRng.FormatConditions.Add xlExpression, Formula1:="=IF(ISTEXT(NOT(SEARCH(""Bank Holiday"",C3:N33))),C3:N33)"
I apologise for the lack of the experience I have and please try not to laugh lol
I am a bit stuck or can't think at the moment. My goal/project is to create a working holiday spreadsheet, that highlights available cells and non-available cells using the Conditional Formatting tool.
In each cell will have the initials of the person and is broken up with the "/" symbol if there is more one person in that cell. My vision is to highlight the blank cells one colour (i.e. Green), then the cells with the symbol "/" another colour (i.e. Red), but for those cells that only have one person to be highlight another colour. The issue I am having is in the range of cells I am working with, I have included Saturdays and Sundays, which I want the formula to avoid. I can not figure this out and I have tried a variety of different formulas such as, ISTEXT. Wondering if somebody could help me with the line #3?
Dim AvaRng As Range
Set AvaRng = Range("C3:N33")
AvaRng.FormatConditions.Delete
'#1
AvaRng.FormatConditions.Add xlExpression, Formula1:="=ISBLANK(C3:N33)"
'#2
AvaRng.FormatConditions.Add xlExpression, Formula1:="=SEARCH(""/"",C3:N33)"
'#3 -
AvaRng.FormatConditions.Add xlExpression, Formula1:="=IF(ISTEXT(NOT(SEARCH(""Bank Holiday"",C3:N33))),C3:N33)"
I apologise for the lack of the experience I have and please try not to laugh lol