Apply Conditional Formatting to Adjacent Cells?

Question

I have some text data I want to search through and highlight.

It is daily / monthly / annual tasks organized (and seperated as groups) by frequency... so I have a seperate box for the daily, weekly, etc.

There are 3 columns for each group... "Type", "Frequency" and "Description".

I have a Listbox that has all the Types of tasks in it, and when you select one and press on a button, it highlights all the tasks that correspond to that...

So far the only way I have been able to do this is through VBA conditional formatting.

But it only highlights the type of task and I haven't been able to figure out how to make it ALSO highlight the two columns next to it...

I was exploring how to do that with a search but could not implement it correctly.

here is my code

Private Sub CommandButton1_Click()
    Dim typeSelection As String
    Dim rng As Range
    Set rng = Sheet4.Range("$E$3:$O$23")
    typeSelection = ListBox1.Text
    Debug.Print rng.Address
    With rng
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlTextString, String:=typeSelection, _
            TextOperator:=xlBeginsWith
        .FormatConditions(1).Interior.Color = RGB(255, 0, 0)
        .FormatConditions(1).Font.Bold = True
        'Debug.Print .FormatConditions(1).AppliesTo.Address
        '.FormatConditions(1).ModifyAppliesToRange .FormatConditions(1).AppliesTo.Offset(RowOffSet:=0, ColumnOffset:=2)
        '.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
        '.FormatConditions(1).Font.Bold = True
        'Debug.Print .FormatConditions(1).AppliesTo.Address
    End With
    Dim rCell As Range
    Dim cRng As Range
    For Each rCell In rng.Cells
        Set cRng = rCell
        Debug.Print rCell.Address
        isConditionallyFormatted (rCell)
    Next rCell
End Sub

Answer 1

I would suggesst something like this :

1. Apply your conditional formatting as in your code
2. Then loop in the range to detect Highlighted and highlight the 2 other horizontaly adjacent cells and pass them in italic to avoid considering them as highlighted by the conditional formating
3. Repass the whole range in not italic

Here is the code :

For Each rCell In rng.Cells
    With rCell
        If .Interior.Color <> RGB(255, 0, 0) Or .Font.Italic <> False Then
        Else
            Range(rCell.Offset(, -1), rCell.Offset(, 1)).Interior.Color = RGB(255, 0, 0)
            Range(rCell.Offset(, -1), rCell.Offset(, 1)).Font.Bold = True
            Range(rCell.Offset(, -1), rCell.Offset(, 1)).Font.Italic = True
        End If
    End With
Next rCell
rng.Font.Italic = False

Answer 2

Ok, as I stated in the comments, by suggestion is to drop macros all together. Sorry if this text feels condescending, but taking your statement of "huge noob", I thought it best to assume you know nothing. Do the following

NOTE! Whenever I put something in brackets and say "write this", do not include the prackets.

Some place, for instance where you have your listbox now, enter all the task types you have or could have. Select all of them, and name that range "taskTypes"

Then, find a cell where you want to have your "button". Give it a nice color, then go to Data tab, select Data validation, and in the "Allow" drop down, select "List". Then, a new text box labeled "Source" will appear. Enter "=taskTypes" there.

Name this cell "taskSelectorCell". This cell will become a drop down list (need to hover over it for the drop down arrow to appear, hence the nice color to see where it is)

Then select the top left cell in your "Daily tasks" table, and create a new conditional formatting rule (Home tab). Select "Use a formula to determine which cells to format". Now, my top left cell in "Daily tasks" was E2, so my conditional formatting formula became:

=$E2=taskSelectorCell

Then select a format format. I chose red background, as you did. Now, this will allow that cell (E2) to become red whenever the taskSelectorCell changes.

To do the final piece, select E2, Press Conditional Formatting and "Manage Rules". The rule you created will be there. In the boc labeled "Applies to", select the daily tasks table and the weekly tasks table (all in one selection), and apply. Now, whenever you select something in the taskSelectorCell dropdown, all taks in those two tables will be marked red, type, frequency and description.

Since you have your groups side by side, we cannot use one formula for all the tables (unless we play with the offset function, but let's not do that if you are a huge excel noob. Therefore, you need to repeat the process twice. Once for the monthly tasks, and once for the "as required" tasks.