change cell colour based on

[Trevor3007]

Hi,

I have attempted this myself but with my very, very limited VB knowledge, cannot get it to work.

Below is a screenshot from the april2022 worksheet, (starting from jan 2022 - feb2023) . its a rather tedious job to colour each cell pne by one with the applicbale background. Can some super VB person come up with solurion to change the cell based on the text within ( for exampe where 'R and Off' is placed in a cell that cell(s) will change the background to yellow) One of the issues I found is when W falls on weekend this must turn 'green' whereas when 'w' is on a mon - friday its white , unless its a 'bank hol' which muxt be red ( as should S be too) The rota starts at e5-ah91. Each month has its own tab ( March22, April22 etc). I also thinking about having a worksheet that had the applicable text in col a & col b would have the colur and the code would look to that . For exanple
Col A Col B
Off VByellow
S VBred
E Vblightblue
H VBpurple

i don my virtual hat to the person(s) who can solve this as ( to me) its not an easy solution?

many thanks in advance

 

[sprigelz]

I would think the most simplest solution would be to create a few Conditional Formatting rules. Are you familiar with this?

 

[Z51]

With as many colors as you have, 8+ by my count, and having multiple criteria for each possible value, using conditional formatting with formulas is going to be tedious. Would probably be best to use a macro (linked to Worksheet_Change event) to update cell colors based on your criteria.

The catch: You need to define every criteria you have in detail, e.g.
Range is E5:AH91 on every sheet
If a cell in that range is "W" AND it is a weekday, cell should be white.
... is "W" AND it is a Bank Holiday (BH in row 3), cell should be red.
... is "W" AND it is a weekend, cell should be green.
If a cell in that range is "R" AND it is a weekday, cell should be....
etc.

With everything defined, you can start creating the rules to act upon them.

 

[Trevor3007]

I would think the most simplest solution would be to create a few Conditional Formatting rules. Are you familiar with this?
View attachment 71480

I am but, would not this to setup for each mont? & what about when W is on a sat or sunday , it has to be green?
thank you

 

[Trevor3007]

With as many colors as you have, 8+ by my count, and having multiple criteria for each possible value, using conditional formatting with formulas is going to be tedious. Would probably be best to use a macro (linked to Worksheet_Change event) to update cell colors based on your criteria.

The catch: You need to define every criteria you have in detail, e.g.
Range is E5:AH91 on every sheet
If a cell in that range is "W" AND it is a weekday, cell should be white.
... is "W" AND it is a Bank Holiday (BH in row 3), cell should be red.
... is "W" AND it is a weekend, cell should be green.
If a cell in that range is "R" AND it is a weekday, cell should be....
etc.

With everything defined, you can start creating the rules to act upon them.

yes.. you are correct, but i dont have the knowledge to do this..sorry

 

[Z51]

People here can help you with the code, but you need to provide the criteria for every one of the colors...

 

[Trevor3007]

People here can help you with the code, but you need to provide the criteria for every one of the colors...

ok... will endevour to supply...thank you

 

[Trevor3007]

People here can help you with the code, but you need to provide the criteria for every one of the colors...

many thanks,
have endevoured to inclue all of the criteria required ( i hope

text	colour
s	red
on	red (when the day falls on a bank holiday)
e	light blue
off	yellow
r	yellow
h	purple
w	white
w	green( when it falls on a sat or sunday)
ot	green
On	blue

 

[Trevor3007]

People here can help you with the code, but you need to provide the criteria for every one of the colors...

many thanks,
have endevoured to inclue all of the criteria required ( i hope

text	colour
s	red
on	red (when the day falls on a bank holiday)
e	light blue
off	yellow
r	yellow
h	purple
w	white
w	green( when it falls on a sat or sunday)
ot	green
On	blue

 

[Z51]

*** Test this on a backup copy of your workbook ***

To use this code: Right-click any worksheet tab and select View Code (or just press ALT+F11). In the VBA code screen, double-click on "This Workbook" on the left, then paste this code into the window on the right. From that point forward, any change you make in cells E5:AH91 on any worksheet in that workbook will trigger this code. (If you don't want it to activate on some worksheets, code can be added to check that as well.)

I've added comments in each section of the code to tell you what it does, which hopefully make sense. I added a check for "T" since I saw some of those on your sample data in post 1. Other entries can be added/removed as needed using a similar format.

I noticed one thing - if a Bank Holiday were to fall on the first of the month... how would you denote that since the month name is in that cell in row 3? My code below doesn't account for that, so maybe you change the location of the Month name to another cell, e.g. E2 or something, and then adjust the code to change any references from E3 to E2.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

' Check to see if the changed cell exists in range E5:AH91 on any worksheet in the workbook
If Not Intersect(Target, Sh.Range("E5:AH91")) Is Nothing Then

    ' If the changed cell is blank, set it's background color to default (no background color)
    If Target.Value = "" Then
        Target.Interior.ColorIndex = xlAutomatic
        Exit Sub
    End If
   
    Dim isWeekday As Boolean, isBankHoliday As Boolean

    ' Check if the value in row 3 for the changed cell is "BH" (Bank Holiday)
    If Sh.Cells(3, Target.Column).Value = "BH" Then
        isBankHoliday = True
    Else
        isBankHoliday = False
    End If
   
    ' Check if the day of the week is a weekday (2-6) or weekend (1, 7)
    ' This is a concatenation of the Month from E3 (e.g. "April"), the Day from row 4 (e.g. 1, 2, 3, 4...)
    ' and the Year from the last four characters of the sheet name, e.g. "2022" from a sheet named "March2022"
    Select Case Weekday(DateValue(Sh.Range("E3") & " " & Sh.Cells(4, Target.Column) & ", " & Right(Sh.Name, 4)))
        Case Is = 1, 7
            isWeekday = False
        Case Else
            isWeekday = True
    End Select

    ' Check the changed cell's value for S, E, R, OFF, OT, T, ON or W and apply the proper Interior color
    Select Case UCase(Target.Value)
        Case Is = "S"
            Target.Interior.ColorIndex = 3 'Red
        Case Is = "E"
            Target.Interior.ColorIndex = 33 'Light Blue
        Case Is = "R", "OFF"
            Target.Interior.ColorIndex = 6 'Yellow
        Case Is = "H"
            Target.Interior.ColorIndex = 29 'Purple
        Case Is = "OT"
            Target.Interior.ColorIndex = 10 'Green
        Case Is = "T"
            Target.Interior.ColorIndex = 8 ' Cyan
        Case Is = "ON"
            If isBankHoliday Then
                Target.Interior.ColorIndex = 3 'Red
            Else
                Target.Interior.ColorIndex = 5 'Blue
            End If
        Case Is = "W"
            If isWeekday Then
                Target.Interior.ColorIndex = 2 'White
            Else
                Target.Interior.ColorIndex = 10 'Green
            End If
        Case Else
            Target.Interior.ColorIndex = xlAutomatic
    End Select
End If

End Sub