Prev: Bypass error message to debug
Next: Data Entry to a Cell Range
From: OssieMac on 6 Mar 2010 06:51
Hi again,

I am still not sure that I fully understand. You say to match colors B3:B10
and then quote "list of 7 products at the top of the page". B3:B10 is 8
cells not 7.

My previous code started at the top of the page for setting the colors.
However your quote "I would finish with a table of data where each row is
colour coded to match the list of 7 products at the top of the page". I am
wondering if R should start at row 11 under the products at the top of the
page and not start from row 1.

If I understand correctly, the user will manually color the cells B3:B10 and
you want to get these colors for each of the matches in cells A3:A10. If
correct, then try the following. (I have started R at row 11)

Sub Decorate()
Dim rngB As Range
Dim rngTemp As Range
Dim c As Range
Dim R As Long
Dim colIdx As Integer
Dim j As Long

Set rngB = Sheets("Sheet2") _
.UsedRange.Columns("A:S")

For j = 3 To 10
For R = 11 To rngB.Rows.Count
If Sheets("Sheet2").Cells(R, 1).Value _
= Sheets("Sheet2").Cells(j, "A") Then

colIdx = Sheets("Sheet2") _
.Cells(j, "B").Interior.ColorIndex

With Sheets("Sheet2")
Set rngTemp = .Range(.Cells(R, 1), _
.Cells(R, "S"))
End With

For Each c In rngTemp
If c.Value > 0 Then
c.Font.Bold = True
With c.Interior
.ColorIndex = colIdx
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
Next c
End If
Next R
Next j
End Sub

'***********************************

Not sure if the following will help but have included it for info because
the ColorIndex matrix provided in Excel help is not accurate.

The code will create a sample of all of the ColorIndex colors. The row
number will be the ColorIndex. Run it on a blank worksheet and you can
actually copy the various cells and use Paste Special -> Formats to put the
colors in your range B3:B10.

Sub IntColIdx()
Dim i As Integer
'Edit "Sheet3" to match your required sheet.
With Sheets("Sheet3")
For i = 1 To 56
.Cells(i, 1).Interior.ColorIndex = i
Next i
End With
End Sub

--
Regards,

OssieMac


From: BeSmart on 6 Mar 2010 09:51
Hi OssieMac

THAT is awesome!!!! Again with a few little changes it's working perfectly!!!!
Your assumptions were spot on!!!
Thank you soooo much!!!
--
Cheers
BeSmart


First  |  Prev  | 
Pages: 1 2
Prev: Bypass error message to debug
Next: Data Entry to a Cell Range