jb
Member
Hi Helpers,
I have prepared my college timetable in excel and I have attached sample sheet here.
My file contains 2 sheets - timetable and formula.
I am pasting daily timetable in "timetable" sheet. There are 14 classes. 2 classes are hidden as no students are there.
Now, I applied 3 conditional formatting. If a faculty goes on leave, his / her lectures cell should be highlighted with green colour.
Now, while doing adjustments of lectures, if a faculty is given 2 or more lectures in same slot (clash) then , his / her lectures cell should be highlighted with yellow colour.
And, if a faculty is busy with some work, he / she is given NA (non availabity). Means if a faculty is not available between 11 to 1 then his / her lectures in those slot will be highlighted with magenta color.
formula sheet extracts faculty names correctly (if it is elective lecture where more than faculty engage students according to their selected ubjects).
b81:r101 in timetable sheet contains formula to detect leave, clash and NA. these also works perfectly.
I applied conditional formatting based on values set in b81:r101.
Now what happens, when I drag cells to do timetable adjustments, conditional formatting of those cells also moves with cells. And conditinoal formatting does not work on new values in those cells.
I have written VBA code to repaint the borders of table cells.
But now I want to update it so that as and when table data change, VBA code should remove conditional formatting from timetable cells and apply conditional formatting again.
Need your help. If you can help me in writing code for small portion of table, rest of code I will do it.
Thanks in advance.
I have prepared my college timetable in excel and I have attached sample sheet here.
My file contains 2 sheets - timetable and formula.
I am pasting daily timetable in "timetable" sheet. There are 14 classes. 2 classes are hidden as no students are there.
Now, I applied 3 conditional formatting. If a faculty goes on leave, his / her lectures cell should be highlighted with green colour.
Now, while doing adjustments of lectures, if a faculty is given 2 or more lectures in same slot (clash) then , his / her lectures cell should be highlighted with yellow colour.
And, if a faculty is busy with some work, he / she is given NA (non availabity). Means if a faculty is not available between 11 to 1 then his / her lectures in those slot will be highlighted with magenta color.
formula sheet extracts faculty names correctly (if it is elective lecture where more than faculty engage students according to their selected ubjects).
b81:r101 in timetable sheet contains formula to detect leave, clash and NA. these also works perfectly.
I applied conditional formatting based on values set in b81:r101.
Now what happens, when I drag cells to do timetable adjustments, conditional formatting of those cells also moves with cells. And conditinoal formatting does not work on new values in those cells.
I have written VBA code to repaint the borders of table cells.
But now I want to update it so that as and when table data change, VBA code should remove conditional formatting from timetable cells and apply conditional formatting again.
Need your help. If you can help me in writing code for small portion of table, rest of code I will do it.
Thanks in advance.