Background colour of cells to change based on value.

[KyleJackMorrison]

Hello,

I have a document (shared) which we use as a calendar and to keep track of people and where they are. It has all 365 day on it and with over 100 people.
I would like a vba to search the document and change the cell colour based on the value. For example: Red if someone is away on holiday. Green if they are on a course.

I use conditional formatting however as people copy and paste in the document, it copies the conditional formation stuff which now has rendered the document unworkable as it crashes due to too many conditional formatting rules.

I have also tried a simple code which colours the cells based on a value, however if someone types "holiday" or "HOLIDAY" it wont change the colour due to the code only searching for "Holiday" in ProperCase.

Is there a more efficient way of having a code where i don't have to put in every type of case search criteria which will slow down the sheet.

Many thanks in advance!

(Current code im using)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)Dim myRange As Range
Dim myCell As Range
Set myRange = Range("G5:NL104")
    For Each myCell In myRange
    If InStr(1, (myCell.Value), "Leave") > 0 Then
    myCell.Interior.ColorIndex = 6
    End If
    For Each myCell In myRange
    If InStr(1, (myCell.Value), "LEAVE") > 0 Then
    myCell.Interior.ColorIndex = 6
    End If
    For Each myCell In myRange
    If InStr(1, (myCell.Value), "leave") > 0 Then
    myCell.Interior.ColorIndex = 6
    End If
    For Each myCell In myRange
    If InStr(1, (myCell.Value), "Holiday") > 0 Then
    myCell.Interior.ColorIndex = 6
    End If
    For Each myCell In myRange
    If InStr(1, (myCell.Value), "HOLIDAY") > 0 Then
    myCell.Interior.ColorIndex = 6
    End If
    For Each myCell In myRange
    If InStr(1, (myCell.Value), "holiday") > 0 Then
    myCell.Interior.ColorIndex = 6
    End If




Next myCell
End Sub

 

[KyleJackMorrison]

I had a selectionchange event on another sheet which i removed, and put in that new code that you've provided.

Thanks for this, unfortunately i have to use merged cells as my work uses this sheet as a calendar of events and merge the cells with relevant info.
But for now it is working to perfect. I'll even leave that error handling message in aha!

Cheers @My Aswer Is This

 

[My Aswer Is This]

Well you may need another feature you did not ask for.

If you enter leave for example and the cell color turns Red but then you change your mine and want to delete the value or change the value to some other value not one of the ones in your choice of words like you enter Take a hike George

Then with this new script the cell color will turn back to no color.

This case Else

Must be the last one

It means if you enter any other value the cell color will be nothing

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  10/16/2018  5:25:19 PM  EDT
If Not Intersect(Target, Range("I6:NN105")) Is Nothing Then
On Error GoTo M
If Target.Cells.CountLarge > 1 Then Exit Sub
With Target.Interior
Select Case Target.Value
Case "Leave", "Holiday"
.ColorIndex = 3
Case "Me", "You", "Us", "They"
.ColorIndex = 8

Case "Her", "Him", "Them"
.ColorIndex = 5
Case Else
.ColorIndex = xlNone

End Select
End With
Exit Sub
M:
MsgBox "Bad Boy using merged cells again?"
End If

End Sub

 

[KyleJackMorrison]

You are a gift that just keeps on giving! Thanks.

Also, what if someone in my sheet was to enter in "Summer Holiday". The script wont change as it's only recognising "Holiday". Is there a way to search and change words containing "holiday"?

 

[My Aswer Is This]

No I really do not know how to do that using Case.

That's why you may need Data Validation lists for those ranges.

It restricts what users can enter.

Like they can only enter Leave or Vacation or such

I assume you know what a Data Validation list is.

 

[KyleJackMorrison]

No worries you've done alot to help me out.

Yeah i use that on a different part of the workbook. I'll just put obvious things in that people might enter if i don't use data validation.

Many thanks :D

 

[My Aswer Is This]

Or with the case statemet enter

"Leave","Summer Leave",Winter Leave"

But not possible with just any thing the user may enter.

Like:
I want my Leave on any day but the day your on leave.


But I