Trying to highlight cells that have the same value as the active c

[Excel_Rookie]

I want to be able to highlight all the cells that have the same text as the
active cell?

So if I'm on G8 and that cell has the word Apples then I want to highlight
all the cells in the E1:E100 range that have Apples in the cell.

 

[Hardeep Kanwar]

You can use Conditional formating

Select the Range E1:E100

Go to Format < Conditional Formatting < Choose < Cell Value is < Equal to <
and Click on G8 it show like this $G8$

Choose Pattern Press Ok

 

[Jacob Skaria]

If you are new to VBA set the Security level to low/medium in
(Tools|Macro|Security). Right click on the sheet tab on which you would like
to have this highligtion and click on 'View Code'. Paste the below code.

I have defined the applicable range as E1:E100 and the range in Col G as
G1:G8. You can change this as per your requirement..

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngTemp As Range
Set rngTemp = Range("E1:E100")
rngTemp.Interior.ColorIndex = xlNone
If Not Application.Intersect(Target, Range("G1:G8")) Is Nothing Then
If Target.Value <> "" Then
For Each cell In rngTemp
If Target.Value = cell.Value Then
cell.Interior.Color = vbYellow
End If
Next
End If
End If
End Sub

If this post helps click Yes

 

[Hardeep Kanwar]

Select Format< conditional formatting< Cell Value is < Equal to
Then Click the Cell .i.e G8

Select the pattern Press Ok

Hardeep kanwar

 

[Excel_Rookie]

What I'm trying to do is only have highlighted cells when the active cell has
the same values in theE1:E100 Range.. here is an example...

I have values

E G
1 Apples Apples
2 Bananas Pears
3 Pears Bananas
4 Pears
5 Apples
6 Apples
7 Apples
8 Apples

When the G1 Cell gets focus the border turns black....I would like to see
the values on the E column be highlighted or Something to indicate that they
have the same value as the cell that is Active under the G column...So in the
above example for G1 Apples then rows E1, E5,E6,E7,E8 would be highlighte
once I would set foucs to the G2 then then rows E1, E5,E6,E7,E8 would not be
highlighted anymore.....

 

[Gord Dibben]

Try this event code. Highlights E1:E100 based on selection of G1, G2 or G3

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "G1:G3"
Dim my_range As Range
Dim cell As Range
Set my_range = Me.Range("E1:E100")
my_range.Interior.ColorIndex = xlnone
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each cell In my_range
If cell.Value = Target.Value Then
cell.Interior.ColorIndex = 3
End If
Next cell
End If
ws_exit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[Excel_Rookie]

How can I do it for the entire workbook ? I don't want to paste the code into
every worksheet within the workboob if I can have it at the workbook level.

thanks

 

[Gord Dibben]

Remove the first line and replace with this.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)

Cut the entire set of code and paste into ThisWorkbook module, not a sheet
module.


Gord Dibben MS Excel MVP

 

[Excel_Rookie]

Thanks that did it.....

Remove the first line and replace with this.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)

Cut the entire set of code and paste into ThisWorkbook module, not a sheet
module.


Gord Dibben MS Excel MVP

 

[Jacob Skaria]

When you 'view code' from sheet tab Worksheet event; it straight away takes
you to the event list of that particular sheet. Similary in the VBE tree view
you can notice the workbook icon. If you double click that the event list are
events for the workbook in general. So any code pasted in those events will
be triggered for the events in any sheet.

Its worth to refer the below link

http://www.mvps.org/dmcritchie/excel/event.htm

If this post helps click Yes