coloring range in one worksheet based on data in another one

  • Thread starter Thread starter ×לי
  • Start date Start date
×

×לי

Hi,

I have in one workbook 2 worksheets that one of them (Prod_list) is the list
of products and in the second worksheet (Used_prod) i need to report if the
product was used.
I am wondering if it is possible to color the range of the specific product
in the products list (Prod_list) while i am reporting it in the other
worksheet (Used_prod)?

Thanks in advance for your time

Eli
 
Eli
Use generic terminology as much as possible. Specifically, what are you
doing when you are "reporting it in the other worksheet (Used_prod)"? HTH
Otto
 
Dear Otto,

In the first table each product is described with few parameters (date,
place and by who) and in the second table I am reporting the name of the
product with few parameters of its usage. I want that when I will type the
name of the product (that exist in the first table) on a certain place in the
second sheet, the row with the same product name in the first sheet will
change its color.

Eli
 
The following macro should do what you want. Not knowing your data, I made
some assumptions. I assumed your first sheet is named "One". I assumed
that the names of the products are in Column A of both sheets starting in A2
down. I assumed the color you want is red. Change these things in the
macro as needed. This macro is a sheet event macro and must be placed in
the sheet module of the second sheet. Access that module by right-clicking
on the sheet tab of the second sheet and selecting View Code. Paste this
macro into that module. "X" out of the module to return to your sheet.
A problem inherent to this type of thing is that you might misspell the
entry in sheet 2. In this case, the code (the macro) will not find the
entry in sheet "One" and nothing will happen. You might give consideration
to using Data Validation in Column A of the second sheet to make your entry.
Post back if you need more. HTH Otto

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rColA As Range
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing And _
Target.Row > 1 Then
With Sheets("One")
Set rColA = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
If Not rColA.Find(What:=Target.Value, LookAt:=xlWhole) Is
Nothing Then
rColA.Find(What:=Target.Value, LookAt:=xlWhole) _
.EntireRow.Interior.ColorIndex = 3
End If
End With
End If
End Sub
 
Back
Top