red Color for same value cells

A

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
 
B

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)
 
F

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
 
B

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)
 
A

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 !!
 
B

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)
 
B

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)
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top