Found a value on another sheet and change color

[stakar]

I have the formula on the worksheet "ShA" cell "BJ3"

=INDEX(BG4:BG801,A2)
where the "A2" contains the result of the formula
= max(A3:A1000)

The result of the =INDEX(BG4:BG801,A2) will be a string value eg
'010'

On another worksheet "ShB" i have a column A with string values as wel
and somewhere in this column there is the result of the above formul
eg '010'.

Using the conditional format option i can use a formula that when i
matches with the specific result value eg. '010' to highlight th
active cell .

I want all the previous to be with vb code because it will be part of
long vb code.

Thanks in advance
Stathi

 

[Frank Kabel]

Hi
one question: why do you want this as VBA solution?. To make this
coloring automatic you'll need an event procedure?

 

[stakar]

Hi Frank
I need this as a vb code because i want to add it in an existing code
IF its an other way to do it except using excel formula i have n
proble

 

[Frank Kabel]

Hi
do you only want the coloring in VBA or also the formula insertion?

 

[stakar]

*Hi
do you only want the coloring in VBA or also the formula insertion?

I want only to color the cell that matches the value. I dont want an
kind of insertion because i ll loose the cell's valu

 

[Frank Kabel]

Hi
try something like the following:
Sub foo()
Dim wks_target As Worksheet
Dim wks_source As Worksheet
Dim rng As Range
Dim cell As Range
Dim lookup_value

Set wks_target = Worksheets("ShB")
Set wks_source = Worksheets("ShA")
lookup_value = wks_source.Range("BJ3").Value

Set rng = wks_target.Range("A1:A100")
For Each cell In rng
If cell.Value = lookup_value Then
cell.Interior.ColorIndex = 3
End If
Next
End Sub

 

[stakar]

*Hi
try something like the following:
Sub foo()
Dim wks_target As Worksheet
Dim wks_source As Worksheet
Dim rng As Range
Dim cell As Range
Dim lookup_value

Set wks_target = Worksheets("ShB")
Set wks_source = Worksheets("ShA")
lookup_value = wks_source.Range("BJ3").Value

Set rng = wks_target.Range("A1:A100")
For Each cell In rng
If cell.Value = lookup_value Then
cell.Interior.ColorIndex = 3
End If
Next
End Sub




--
Regards
Frank Kabel
Frankfurt, Germany


Frank
at this point
lookup_value = wks_source.Range("BJ3").Value
I want the code to run the formula that finds the value that it has t
look for next

Stathi

 

[Frank Kabel]

Hi
you have to decide if you want a formula approach or a VBA solution.
IThe code after this line will find all matching values and will color
them.

--
Regards
Frank Kabel
Frankfurt, Germany

 

[stakar]

*Hi
you have to decide if you want a formula approach or a VBA solution.
IThe code after this line will find all matching values and wil
color
them.

--
Regards
Frank Kabel
Frankfurt, Germany

Hi Frank
I want a vb solution but to do the job like working with the formul