Have the value in a cell cause the same value in an array of data to light up red when value is equal

[Hutchcove]

Hard to explain what I'm looking for or if it is even possible.

B5 is the cell in which I have any value #.# (ex. 17.2)

I have an array of data from J5 to AB5 which different values
Another array from J10 to AB10 with different values and so forth
Currently these arrays are not one on top of the other (Can be done if it makes the formatting possible)

Expectations:
When the value in B5 is 17.2 and the same value exists in the arrays that I have in place, I woiuld like the array value of 17.2 to light up red. Moreover, if the value repeats in another array for the same value- it too light up red.

I hope this is clear but please let me know if you require additional information

 

[alansidman]

Here is VBA solution for you

Option Explicit


Sub Hutch()
    Dim c As Range, rng As Range
    Dim crit As Variant
    crit = Range("B5")
    Set rng = Range("J5:AB5", "J10:AB10")
    For Each c In rng
        If c = crit Then
            c.Interior.ColorIndex = 3
        End If
    Next c


End Sub

 

[Scott T]

Select your ranges

In conditional formatting
choose new rule then
Select use formula to determine which cells to format
use
=$B$5=J5

select your formatting and click OK

The VBA code above would have to be manually ran to to do the formatting unless it was changed to a change event.