red Color for same value cells

[Aristotele64]

Hi from italy,
sorry if question was already posted but i dont'find anithing on goole :
I have a column H1:H:32, example :

h1 Alfa
h2 Beta
h3 Omega
h4 Beta
h5 Omega
h6 Alfa
h7 P
h8 Alfa
h9 P
h10 ecc

when I click on cell taht contain "Alfa" , i want all "alfa" cell become red
if i click on cell that contain "Beta" , all "Beta" cell become red


TIA

 

[Bob Phillips]

Hi from the UK,

here is one way

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cell As Range
If Not Intersect(Target, Range("H1:H:32")) Is Nothing Then
Range("B1:B100").FormatConditions.Delete
For Each cell In Range("H1:H:32")
cell.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & cell.Address & "=" & Target.Address
cell.FormatConditions(1).Interior.ColorIndex = 3
Next cell
End If

End Sub

it is worksheet event code, so right-click on the sheet tab, select View
Code from the menu, and paste the code in

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi
though this is possible (using the Selection_change event) this is not
that easy. It would be easier if you could enter the value in a
separate cell and use conditional format for this

 

[Bob Phillips]

Frank,

See my answer.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Aristotele64]

Many tkanks !!!!
It's working beatiful !!!!
I change only :

Range("B1:B100").FormatConditions.Delete in
Range("H2:H32").FormatConditions.Delete

right ?

by and good evining !!

 

[Frank Kabel]

Bob
saw it - neat BTW

I was just too lazy...

 

[Bob Phillips]

I tested on B1:B100 and changed just before I posted it. Missed that one.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

if only I could get rid of the iterating through all the cells!

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Select H1:H32

with H1 the activeCell

format=>Conditional formatting

formula is: =INDIRECT(CELL("address"))=H1

make red pattern the format.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("H1:H32")) Is Nothing Then
ActiveSheet.Calculate
End If
End Sub

 

[Frank Kabel]

Hi bob
maybe changing the conditional format formula via code