PDA

View Full Version : Distinguish between two similar values



Klartigue
08-31-2011, 08:13 AM
The code I have is meant to leave the first two A1, the first two A2, etc..
But now I want to distinguish between each. How do i have the first A1, A2, A3, etc.. be highlighted in yellow and the second A1, A2, A3, etc...be renamed "cover"? I have a formula for this but it is too specific for the rows.
Sub highlight()
'
' highlight Macro
'
'
Rows("2:2").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Rows("4:4").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Rows("6:6").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Rows("8:8").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Rows("10:10").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Rows("12:12").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub
I need a more general formula that references each top A1, A2, A3, etc...

Thank you so much for your help! Greatly appreciate.

Bob Phillips
08-31-2011, 09:24 AM
Sub highlight()
Dim lastrow As Long
Dim i As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow

If Application.CountIf(.Range("A1").Resize(i), .Cells(i, "A").Value) = 1 Then

With .Cells(i, "A").Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
Next i
End With
End Sub

Klartigue
08-31-2011, 09:33 AM
Thanks that works! Can you help me write a macro that renames the second A1, A2, A3, etc.. the word "cover"

Bob Phillips
08-31-2011, 09:54 AM
Sub highlight()
Dim lastrow As Long
Dim i As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow

If Application.CountIf(.Range("A1").Resize(i), .Cells(i, "A").Value) = 1 Then

With .Cells(i, "A").Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ElseIf Application.CountIf(.Range("A1").Resize(i), .Cells(i, "A").Value) = 2 Then

.Cells(i, "A").Value = "cover"
End If
Next i
End With
End Sub