Hi,

Say in an Excel column I have this:

B1 12
B2 12
B3 4
B4 7
B5 4
B6 12
B7 7

and want to use a VBA piece of code to give same color to those with
same value. How can I do so please?

Regards,
Mike

If you do not have more than 3 groups or single values to color then use
Conditonal Formatting don't need to use VBA.

Otherwise one way of doing this is as follows........
I have shown various ways of setting the Case to a single value, a series of
non-contiguous vaues, a range or a conditional operator. For individual
values just use Case 1, Case 2 etc as shown in the first two instances.

Note you are limited to 56 colours but you might change the font color or
fill patterns etc to extend this range.


Sub ColorCells()
Dim i As Long
For i = 1 To 7
With Cells(i, 2) ' the 2 refers to column 2 (B)
Select Case .Value
Case 1
.Interior.ColorIndex = 1
.Interior.Pattern = xlSolid
Case 2
.Interior.ColorIndex = 2
.Interior.Pattern = xlSolid
Case 3, 4
.Interior.ColorIndex = 3
.Interior.Pattern = xlSolid
Case 5 To 8
.Interior.ColorIndex = 4
.Interior.Pattern = xlSolid
Case Is 8
.Interior.ColorIndex = 5
.Interior.Pattern = xlSolid
End Select
End With
Next i
End Sub

Cheers
Nigel

"Michael" wrote in message
om...
Hi,

Say in an Excel column I have this:

B1 12
B2 12
B3 4
B4 7
B5 4
B6 12
B7 7

and want to use a VBA piece of code to give same color to those with
same value. How can I do so please?

Regards,
Mike

Hi Michael,

Michael wrote:
Say in an Excel column I have this:

B1 12
B2 12
B3 4
B4 7
B5 4
B6 12
B7 7

and want to use a VBA piece of code to give same color to those with
same value. How can I do so please?


try this :

Sub ColoredSameValues()
Dim rngArea As Range
Dim rngCellA As Range
Dim rngCellB As Range
Dim colValue As New Collection
Dim intColor As Integer

Set rngArea = ActiveSheet.Range("B1:B50")
intColor = 5
On Error Resume Next
For Each rngCellA In rngArea
If rngCellA.Value < "" Then
Err.Clear
colValue.Add rngCellA.Value, "MB" & rngCellA.Value
If Err = 0 Then
intColor = intColor + 1
For Each rngCellB In rngArea
If rngCellB.Value = rngCellA.Value Then
rngCellB.Interior.ColorIndex = intColor
End If
Next rngCellB
End If
End If
Next rngCellA
End Sub

--
Regards
Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

Hi,

You could first pull a unique list of your Excel Column. Loop through
your unique list and compare each value in the excel column. If it
matches, color it, else skip. Here's some sample code: "Values" is the
range which has all the Excel column entries. "Extracts" is the range
to which unique values are extracted.

Sub Test()

Dim intUniqueCounter As Integer, intRowCounter As Integer,
intUniqueValue As Integer
Dim intColor As Integer

intColor = 5

'Filter unique list of values
Range("Values").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range("Extract" _
), Unique:=True

'Loop through each unique value
For intUniqueCounter = 1 To Range("Extract").CurrentRegion.Rows.Count
- 1

'Increment color with each unique value
intColor = intColor + 1
intUniqueValue =
Range("Extract").CurrentRegion.Cells(intUniqueCoun ter + 1, 1).Value

'Loop through each cell in column
For intRowCounter = 1 To Range("Values").CurrentRegion.Rows.Count
- 1

'Compare values
If Range("Values").Cells(intRowCounter + 1, 1).Value =
intUniqueValue Then

Range("Values").Cells(intRowCounter + 1, 1).Select
With Selection.Interior
.ColorIndex = intColor
.Pattern = xlSolid
End With
End If
Next
Next
End Sub


Hope this helps!

Varsha
Hi,

Say in an Excel column I have this:

B1 12
B2 12
B3 4
B4 7
B5 4
B6 12
B7 7

and want to use a VBA piece of code to give same color to those with
same value. How can I do so please?

Regards,
Mike