excel conditional formatting based on active cell

Question

I'm trying to easily represent a relationship between the active cell, and a set of other cells, preferably using conditional formatting.

I'm essentially making a Gantt chart, and would like to be able to shade cells a different color to identify dependencies. So the idea is that in Column A I have a set of task IDs, and in Column B I have the description, and Column C is a set of dependencies.

When I have something like 'B3' selected, it'd look at the value in C3 (a comma-separated list of ID's contained within Column A) to determine which matching rows, as identified by the ID's in Column A, are dependencies - then color those differently.

This would switch based on the active cell.

I'm guessing this isn't doable with conditional formatting, and may need to do some VBA or come up with a different way to identify these relationships. Has anybody done anything similar?

Let's say I have my simple table set up something like this:

Task ID | Description | Dependencies
--------|-------------|-------------
1       | Task One    |     
2       | Task Two    | 1
3       | Task Three  |
4       | Task Four   | 1, 3
5       | Task Five   | 1, 2, 4

When I have the "1, 3" cell selected (next to Task Four) I'd want Task One and Task Three to be highlighted.

I added some VBA code to update some conditional formatting in real time.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Target.Calculate
    End Sub

I have some conditional formatting working to highlight a different cell in the same row as what I've got as the active cell:

=CELL("address")=CELL("address",$C2)

Applies to the column I want to highlight, Column B

But I don't want to always highlight the adjacent cell...I want to find the proper tasks based on the values in the Dependencies column.

Added one, but can't figure out how to do the vlookup stuff. — Dave
Added the VBA code to update the formatting which works good, and added the conditional formatting I have right now which highlights the adjacent cell, but I'd like to use a vlookup or something else to figure out which ones to highlight. — Dave
You don't need and we don't want (solved) or any other meta noise in the title. You have accepted an answer, that is all that is needed. — rene

Scott Craner Scott Craner · Accepted Answer · 2017-07-27T18:15:35

use this Event in the worksheet code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As Variant
Dim strarr() As String
Dim j As Long
If Not Intersect(Target, Range("C:C")) Is Nothing Then
    Range("B:B").Interior.Pattern = xlNone
    If Target <> "" Then
        strarr = Split(Target, ",")
        For Each str In strarr
            j = 0
            On Error Resume Next
            j = Application.WorksheetFunction.Match(CLng(Application.Trim(str)), Range("A:A"), 0)
            On Error GoTo 0
            If j <> 0 Then
                Cells(j, 2).Interior.Color = 65535
            End If
        Next str
    End If
End If

End Sub

excel conditional formatting based on active cell

1 Answers