If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Trying to highlight cells that have the same value as the active c
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. |
#2
|
|||
|
|||
Trying to highlight cells that have the same value as the active c
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 "Excel_Rookie" wrote: 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. |
#3
|
|||
|
|||
Trying to highlight cells that have the same value as the active c
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 --------------- Jacob Skaria "Excel_Rookie" wrote: 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. |
#4
|
|||
|
|||
Trying to highlight cells that have the same value as the active c
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" wrote: 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. |
#5
|
|||
|
|||
Trying to highlight cells that have the same value as the acti
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..... |
#6
|
|||
|
|||
Trying to highlight cells that have the same value as the acti
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 On Thu, 11 Jun 2009 15:13:03 -0700, Excel_Rookie wrote: 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..... |
#7
|
|||
|
|||
Trying to highlight cells that have the same value as the acti
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 "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Excel_Rookie" wrote: 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. |
#8
|
|||
|
|||
Trying to highlight cells that have the same value as the acti
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 On Thu, 11 Jun 2009 16:28:01 -0700, Excel_Rookie wrote: 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 "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Excel_Rookie" wrote: 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. |
#9
|
|||
|
|||
Trying to highlight cells that have the same value as the acti
Thanks that did it.....
"Gord Dibben" wrote: 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 On Thu, 11 Jun 2009 16:28:01 -0700, Excel_Rookie wrote: 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 "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Excel_Rookie" wrote: 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. |
#10
|
|||
|
|||
Trying to highlight cells that have the same value as the acti
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 --------------- Jacob Skaria "Excel_Rookie" wrote: 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 "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Excel_Rookie" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|