Hello all,

I have a simple set like this:

A B C D E F ----- rng3
1.1 1 1 1 0 0 3
1.2 1 0 0 1 0 2
1.3 1 0 0 0 0 1
2.1 0 1 1 1 1 4
2.2 1 1 1 1 1 5
Rank 2 5 1 6 3 4 ----- rng1d

Assuming that letters A, C and D are highlighted (colorindex = 3), I
want to transform the same pattern to the corresponding cells in the
row starting with Rank. To achieve this I have written a code like
this:

Set rng1c = Sheet1.Columns(1).Find( _
What:="Rank", _
After:=Cells(Rows.Count, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Set rng1d = rng1c.EntireRow

' rng1d.Interior.ColorIndex = 3 --- this highlights the entire row
starting with 'Rank'

But, I want the corresponding cells in the rng1d to be highlighted
according to the cells that have been highlighted in rng3.

For example:

1. As we have assumed that A, C and D have been highlighted, the values
2,1,6 have to be highlighted in rng 1d respectively.

2. If we assume B, D and E is highlighted, the values 5,6,3 have to be
highlighted in rng 1d respectively.

I tried a code like this.. It doesnt work for some reason..

rng1d.Cells.Interior.ColorIndex = rng3.Cells.Interior.ColorIndex

I would deeply appreciate any help for this.

Thanks,
Thulasiram

Try this code:

Sub test()
Dim hlitrng As Range, rng3 As Range, rng1d As Range
rankRow = Columns(1).Find( _
What:="Rank", _
After:=Cells(Rows.Count, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Row
Set rng1d = Range("B" & rankRow & ":G" & rankRow)
Set rng3 = Range("B1:G1")
For Each cella In rng3
If cella.Interior.ColorIndex = 3 Then
If hlitrng Is Nothing Then
Set hlitrng = Intersect(rng1d, cella.EntireColumn)
Else
Set hlitrng = Union(hlitrng, Intersect(rng1d,
cella.EntireColumn))
End If
End If
Next cella
hlitrng.Interior.ColorIndex = 3
End Sub

Regards,
Stefi

€žThulasiram€� ezt Ã*rta:

Hello all,

I have a simple set like this:

A B C D E F ----- rng3
1.1 1 1 1 0 0 3
1.2 1 0 0 1 0 2
1.3 1 0 0 0 0 1
2.1 0 1 1 1 1 4
2.2 1 1 1 1 1 5
Rank 2 5 1 6 3 4 ----- rng1d

Assuming that letters A, C and D are highlighted (colorindex = 3), I
want to transform the same pattern to the corresponding cells in the
row starting with Rank. To achieve this I have written a code like
this:

Set rng1c = Sheet1.Columns(1).Find( _
What:="Rank", _
After:=Cells(Rows.Count, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Set rng1d = rng1c.EntireRow

' rng1d.Interior.ColorIndex = 3 --- this highlights the entire row
starting with 'Rank'

But, I want the corresponding cells in the rng1d to be highlighted
according to the cells that have been highlighted in rng3.

For example:

1. As we have assumed that A, C and D have been highlighted, the values
2,1,6 have to be highlighted in rng 1d respectively.

2. If we assume B, D and E is highlighted, the values 5,6,3 have to be
highlighted in rng 1d respectively.

I tried a code like this.. It doesnt work for some reason..

rng1d.Cells.Interior.ColorIndex = rng3.Cells.Interior.ColorIndex

I would deeply appreciate any help for this.

Thanks,
Thulasiram

Stefi,

Just tried your code.. It works perfect and does what I was looking
for. Thanks for your help. I appreciate it...


Stefi wrote:
Try this code:

Sub test()
Dim hlitrng As Range, rng3 As Range, rng1d As Range
rankRow = Columns(1).Find( _
What:="Rank", _
After:=Cells(Rows.Count, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Row
Set rng1d = Range("B" & rankRow & ":G" & rankRow)
Set rng3 = Range("B1:G1")
For Each cella In rng3
If cella.Interior.ColorIndex = 3 Then
If hlitrng Is Nothing Then
Set hlitrng = Intersect(rng1d, cella.EntireColumn)
Else
Set hlitrng = Union(hlitrng, Intersect(rng1d,
cella.EntireColumn))
End If
End If
Next cella
hlitrng.Interior.ColorIndex = 3
End Sub

Regards,
Stefi

,,Thulasiram" ezt írta:

Hello all,

I have a simple set like this:

A B C D E F ----- rng3
1.1 1 1 1 0 0 3
1.2 1 0 0 1 0 2
1.3 1 0 0 0 0 1
2.1 0 1 1 1 1 4
2.2 1 1 1 1 1 5
Rank 2 5 1 6 3 4 ----- rng1d

Assuming that letters A, C and D are highlighted (colorindex = 3), I
want to transform the same pattern to the corresponding cells in the
row starting with Rank. To achieve this I have written a code like
this:

Set rng1c = Sheet1.Columns(1).Find( _
What:="Rank", _
After:=Cells(Rows.Count, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Set rng1d = rng1c.EntireRow

' rng1d.Interior.ColorIndex = 3 --- this highlights the entire row
starting with 'Rank'

But, I want the corresponding cells in the rng1d to be highlighted
according to the cells that have been highlighted in rng3.

For example:

1. As we have assumed that A, C and D have been highlighted, the values
2,1,6 have to be highlighted in rng 1d respectively.

2. If we assume B, D and E is highlighted, the values 5,6,3 have to be
highlighted in rng 1d respectively.

I tried a code like this.. It doesnt work for some reason..

rng1d.Cells.Interior.ColorIndex = rng3.Cells.Interior.ColorIndex

I would deeply appreciate any help for this.

Thanks,
Thulasiram