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

  • Thread starter Thread starter Excel_Rookie
  • Start date Start date
E

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.
 
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
 
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
 
Select Format< conditional formatting< Cell Value is < Equal to
Then Click the Cell .i.e G8

Select the pattern Press Ok

Hardeep kanwar
 
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.....
 
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
 
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
 
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
 
Thanks that did it.....

Gord Dibben said:
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
Click to expand...
 
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
 
Back
Top