Conditional Formatting based on certain values within cells

furnitureguy1 · Aug 16, 2018

Hello, I have an employee vacation worksheet where I need cells to become highlighted if two or more people in the same group are taking the same days off. One cell may contain 1,2,3,4 and the other may contain only 3...but since they both share the number 4 I need them both to highlight red. I have diagrammed what I am talking about below.

Thank you in advance for your help!

Randy	1,2,3,4
John
Beth	3
Billy	5
Dan

lrobbo314 · Aug 16, 2018

I think that a situation like this is where PowerQuery really shines, if you have it available. This doesn't do any highlighting, but it does put it into a pivot table where you can easily see who has what days off, and more importantly, who has the same day off. Here are the steps.

Select your data and on the 'Data' tab under 'Get & Transform' click 'From Table'.

Then in the editor, follow these steps.

Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(Source, {{"Days", type text}}, "en-US"), "Days", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Days.1", "Days.2", "Days.3", "Days.4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Days.1", Int64.Type}, {"Days.2", Int64.Type}, {"Days.3", Int64.Type}, {"Days.4", Int64.Type}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Days.1", "Days.2", "Days.3", "Days.4"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Only Selected Columns",{"Attribute"})
in
    #"Removed Columns"

In the editor, under the 'Home' tab, click 'Close and Load To' and select 'Only Create Connection' and select 'Add to Data Model'.

Then, under the 'Data' tab, select 'Manage Data Model', and select 'Pivot Table'.

At that point it works just like any other Pivot Table and you can get the results below.

I know it seems like a lot of steps, but once you play around with PowerQuery, it is actually really easy.

	A	B	C	D	E
1	Name	Days		Day	Name
2	Randy	1,2,3,4		1	Randy
3	John			2	Randy
4	Beth	3		3	Beth
5	Billy	5			Randy
6	Dan			4	Randy
7				5	Billy

furnitureguy1 · Aug 16, 2018

Thank you Irobbo, but unfortunately this option won't really help me for the sheer fact that I would have to change the information in the editor over 200 times (52 weeks x 4 teams). My mistake for not posting a larger sample size of what I have, but as you can see the way it is currently being used, the numbers aren't always going to be 1-5 or even 1-31, it is separated by weeks. Sorry again for the confusion.

Employee Name	June	June	June	June	July
	4-8	11-15	18-22	25-29	2-6
Randy	4,5,6		20		4
John		11		25,26,27
Beth	5			27,28,29
Billy			18,19,20		4,5

lrobbo314 · Aug 16, 2018

Works essentially the same way. The only change I made to your original data was to include row 2 with row 1 as you can see in the table below. There are a couple extra steps in the editor, but the whole thing only took a couple of minutes to put together. If you're interested in this approach, let me know and I will try to walk you through it. Otherwise, I would imagine that conditional formatting won't be able to handle it. Probably would need to use VBA.
<table valign="top" border="1"><caption>LEGO HTML</caption><col width="54"><col width="169"><col width="131"><col width="141"><col width="141"><col width="141"><col width="127"><col width="124"><col width="145"><col width="124"><col width="170">
<tr><td></td><th style="background-color:rgb(166, 166, 166)">A</th><th style="background-color:rgb(166, 166, 166)">B</th><th style="background-color:rgb(166, 166, 166)">C</th><th style="background-color:rgb(166, 166, 166)">D</th><th style="background-color:rgb(166, 166, 166)">E</th><th style="background-color:rgb(166, 166, 166)">F</th><th style="background-color:rgb(166, 166, 166)">G</th><th style="background-color:rgb(166, 166, 166)">H</th><th style="background-color:rgb(166, 166, 166)">I</th><th style="background-color:rgb(166, 166, 166)">J</th></tr>
<tr><th style="background-color:rgb(166, 166, 166)">1</th><td style="background-color:rgb(255, 255, 255)">Employee Name</td><td style="background-color:rgb(255, 255, 255)">June 4-8</td><td style="background-color:rgb(255, 255, 255)">June 11-15</td><td style="background-color:rgb(255, 255, 255)">June 18-22</td><td style="background-color:rgb(255, 255, 255)">June 25-29</td><td style="background-color:rgb(255, 255, 255)">July 2-6</td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(91, 155, 213)">Date Range</td><td style="background-color:rgb(91, 155, 213)">Day</td><td style="background-color:rgb(91, 155, 213)">Employee Name</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">2</th><td style="background-color:rgb(255, 255, 255)">Randy</td><td style="background-color:rgb(255, 255, 255)">4,5,6</td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)">20</td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)">4</td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(155, 194, 230)">July 2-6</td><td style="background-color:rgb(189, 215, 238)">4</td><td style="background-color:rgb(255, 255, 255)">Billy</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">3</th><td style="background-color:rgb(255, 255, 255)">John</td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)">11</td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)">25,26,27</td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(155, 194, 230)"></td><td style="background-color:rgb(189, 215, 238)"></td><td style="background-color:rgb(255, 255, 255)">Randy</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">4</th><td style="background-color:rgb(255, 255, 255)">Beth</td><td style="background-color:rgb(255, 255, 255)">5</td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)">27,28,29</td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(155, 194, 230)"></td><td style="background-color:rgb(189, 215, 238)">5</td><td style="background-color:rgb(255, 255, 255)">Billy</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">5</th><td style="background-color:rgb(255, 255, 255)">Billy</td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)">18,19,20</td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)">4,5</td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(155, 194, 230)">June 11-15</td><td style="background-color:rgb(189, 215, 238)">11</td><td style="background-color:rgb(255, 255, 255)">John</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">6</th><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(155, 194, 230)">June 18-22</td><td style="background-color:rgb(189, 215, 238)">18</td><td style="background-color:rgb(255, 255, 255)">Billy</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">7</th><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(155, 194, 230)"></td><td style="background-color:rgb(189, 215, 238)">19</td><td style="background-color:rgb(255, 255, 255)">Billy</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">8</th><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(155, 194, 230)"></td><td style="background-color:rgb(189, 215, 238)">20</td><td style="background-color:rgb(255, 255, 255)">Billy</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">9</th><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(155, 194, 230)"></td><td style="background-color:rgb(189, 215, 238)"></td><td style="background-color:rgb(255, 255, 255)">Randy</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">10</th><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(155, 194, 230)">June 25-29</td><td style="background-color:rgb(189, 215, 238)">25</td><td style="background-color:rgb(255, 255, 255)">John</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">11</th><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(155, 194, 230)"></td><td style="background-color:rgb(189, 215, 238)">26</td><td style="background-color:rgb(255, 255, 255)">John</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">12</th><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(155, 194, 230)"></td><td style="background-color:rgb(189, 215, 238)">27</td><td style="background-color:rgb(255, 255, 255)">Beth</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">13</th><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(155, 194, 230)"></td><td style="background-color:rgb(189, 215, 238)"></td><td style="background-color:rgb(255, 255, 255)">John</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">14</th><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(155, 194, 230)"></td><td style="background-color:rgb(189, 215, 238)">28</td><td style="background-color:rgb(255, 255, 255)">Beth</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">15</th><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(155, 194, 230)"></td><td style="background-color:rgb(189, 215, 238)">29</td><td style="background-color:rgb(255, 255, 255)">Beth</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">16</th><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(155, 194, 230)">June 4-8</td><td style="background-color:rgb(189, 215, 238)">4</td><td style="background-color:rgb(255, 255, 255)">Randy</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">17</th><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(155, 194, 230)"></td><td style="background-color:rgb(189, 215, 238)">5</td><td style="background-color:rgb(255, 255, 255)">Beth</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">18</th><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(155, 194, 230)"></td><td style="background-color:rgb(189, 215, 238)"></td><td style="background-color:rgb(255, 255, 255)">Randy</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">19</th><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(155, 194, 230)"></td><td style="background-color:rgb(189, 215, 238)">6</td><td style="background-color:rgb(255, 255, 255)">Randy</td></tr></table>

lrobbo314 · Aug 16, 2018

Speaking of a VBA solution, I think I might have one for you.

Here's the code.

Code:

Sub test()
Dim R       As Range: Set R = Range("B3:F" & Range("A" & Rows.Count).End(xlUp).Row)
Dim Col     As Range: Set Col = R.Cells(1, 1)
Dim D1      As New Dictionary
Dim D2      As New Dictionary
Dim SP1()   As String
Dim SP2()   As String
Dim Cur     As String


Set Col = Col.Resize(R.Rows.Count, 1)


For c = 1 To R.Columns.Count
    For i = 1 To Col.Rows.Count
        Cur = Col.Cells(i, 1).Address
        SP1 = Split(Col.Cells(i, 1), ",")
        Add2Dict D1, SP1
        If UBound(SP1) > -1 Then
            For j = 1 To Col.Rows.Count
                If Cur <> Col.Cells(j).Address Then
                    SP2 = Split(Col.Cells(j), ",")
                    If UBound(SP2) > -1 Then
                        Add2Dict D2, SP2
                        If Compare(D1, D2) Then
                            Col.Cells(j).Interior.ColorIndex = 6
                        End If
                    End If
                End If
            Next j
        End If
        ClearDict D1
        ClearDict D2
    Next i
    Set Col = Col.Offset(, 1)
Next c


End Sub


Sub Add2Dict(D As Dictionary, AR As Variant)
For i = 0 To UBound(AR)
    D.Add AR(i), AR(i)
Next i
End Sub


Sub ClearDict(D As Dictionary)
D.RemoveAll
End Sub


Function Compare(D1 As Dictionary, D2 As Dictionary) As Boolean
Dim Res As Boolean: Res = False
For i = 0 To D2.Count -1
    If D1.Exists(D2.Items(i)) Then
        Res = True
        Exit For
    End If
Next i
Compare = Res
End Function

And here's what the results look like.
<table valign="top" border="1"><caption>LEGO HTML</caption><col width="54"><col width="158"><col width="108"><col width="108"><col width="119"><col width="119"><col width="108">
<tr><td></td><th style="background-color:rgb(166, 166, 166)">A</th><th style="background-color:rgb(166, 166, 166)">B</th><th style="background-color:rgb(166, 166, 166)">C</th><th style="background-color:rgb(166, 166, 166)">D</th><th style="background-color:rgb(166, 166, 166)">E</th><th style="background-color:rgb(166, 166, 166)">F</th></tr>
<tr><th style="background-color:rgb(166, 166, 166)">1</th><td style="background-color:rgb(255, 255, 255)">Employee Name</td><td style="background-color:rgb(255, 255, 255)">June</td><td style="background-color:rgb(255, 255, 255)">June</td><td style="background-color:rgb(255, 255, 255)">June</td><td style="background-color:rgb(255, 255, 255)">June</td><td style="background-color:rgb(255, 255, 255)">July</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">2</th><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)">4-8</td><td style="background-color:rgb(255, 255, 255)">11-15</td><td style="background-color:rgb(255, 255, 255)">18-22</td><td style="background-color:rgb(255, 255, 255)">25-29</td><td style="background-color:rgb(255, 255, 255)">2-6</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">3</th><td style="background-color:rgb(255, 255, 255)">Randy</td><td style="background-color:rgb(255, 255, 0)">4,5,6</td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 0)">20</td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 0)">4</td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">4</th><td style="background-color:rgb(255, 255, 255)">John</td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)">11</td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 0)">25,26,27</td><td style="background-color:rgb(255, 255, 255)"></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">5</th><td style="background-color:rgb(255, 255, 255)">Beth</td><td style="background-color:rgb(255, 255, 0)">5</td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 0)">27,28,29</td><td style="background-color:rgb(255, 255, 255)"></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)">6</th><td style="background-color:rgb(255, 255, 255)">Billy</td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 0)">18,19,20</td><td style="background-color:rgb(255, 255, 255)"></td><td style="background-color:rgb(255, 255, 0)">4,5</td></tr></table>

lrobbo314 · Aug 16, 2018

Forgot to mention, you will need to add a reference to 'Microsoft Scripting Runtime'.

furnitureguy1 · Aug 17, 2018

Thank you again for your help! I think this is going to be the way to go, and I modified the code you sent a little bit to include the entire range that my spreadsheet entails, but I keep getting a run-time error at this point in the code. 'Run-time error 457': This key is already associated with an element of this collection. Any idea how I can fix it?

Code:

[FONT=Verdana]Sub Add2Dict(D As Dictionary, AR As Variant)[/FONT]
For i = 0 To UBound(AR)
    [COLOR=#ff0000]D.Add AR(i), AR(i)[/COLOR]
Next i
End Sub

lrobbo314 · Aug 17, 2018

Change the first argument from AR(i) to just 'i'. That might work. If it doesn't, give me some sample data that's causing the error and I'll troubleshoot it.

lrobbo314 · Aug 17, 2018

Actually, thinking about it... It's not best practices, but if you add this line at the top of the sub it'll probably work.

On error resume next

furnitureguy1 · Aug 17, 2018

Is there any way I could just get the file itself to you? I tried re-editing the sub as you mentioned and nothing worked. Even if I am able to get it working, the bigger problem is going to be that I need each team to operate independently. Here is a picture of part of the spreadsheet.

https://ibb.co/d7K0Az

Conditional Formatting based on certain values within cells

furnitureguy1

New Member

Excel Facts

lrobbo314

Well-known Member

furnitureguy1

New Member

lrobbo314

Well-known Member

lrobbo314

Well-known Member

lrobbo314

Well-known Member

furnitureguy1

New Member

lrobbo314

Well-known Member

lrobbo314

Well-known Member

furnitureguy1

New Member

Similar threads

Forum statistics

Share this page

Conditional Formatting based on certain values within cells

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

Well-known Member

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock