change text color when the value is changed in a column

[gtd526]

Hello,
Looking to apply a text color (via Conditional Formatting or Macro) when the value changes in a column.
2,3 & 4 will be colored, only the First value.
Thank you

Book1
	A
1	1
2	1
3	2
4	2
5	3
6	3
7	4
8	4
Sheet1

 

[Skyybot]

Try this on a copy of your Workbook.

Option Explicit

Sub ChangeFirstNewValueColor()
Dim rng As Range, cell As Range
Set rng = ActiveSheet.UsedRange
On Error Resume Next
For Each cell In rng
    If Not cell.Value = cell.Offset(-1, 0).Value Then cell.Font.Color = vbRed
Next cell
End Sub

 

[Fuji]

Avoid use of On Error statement,

Sub test()
    Dim r As Range, temp
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        .Font.ColorIndex = xlAutomatic
        For Each r In .Cells
            If r <> temp Then r.Font.Color = vbRed: temp = r
        Next
    End With
End Sub

 

[gtd526]

Try this on a copy of your Workbook.

Option Explicit

Sub ChangeFirstNewValueColor()
Dim rng As Range, cell As Range
Set rng = ActiveSheet.UsedRange
On Error Resume Next
For Each cell In rng
    If Not cell.Value = cell.Offset(-1, 0).Value Then cell.Font.Color = vbRed
Next cell
End Sub

It works. Thank you.
How can I adjust so it acts on the row?
The column to look for the change is "D", when that value changes.

Book1
	A	B	C	D	E
1	Header1	Header2	Header3	Header4	Header5
2	ORL	7	no	1	same
3	MIL	-7	no	1	diff
4	DAL	4	yes	1	diff
5	OKC	-4	no	2	diff
6	ATL	7.5	no	2	same
7	NYK	-7.5	yes	2	same
8	GSW	2	yes	3	same
9	HOU	-2	yes	3	diff
10	DET	13.5	yes	3	diff
11	BOS	-13.5	no	3	diff
12	TOR	11	no	4	diff
13	MIA	-11	no	4	diff
14	SAC	-5.5	yes	4	same
15	NOP	5.5	yes	4	same
Sheet1

 

[gtd526]

Avoid use of On Error statement,

Sub test()
    Dim r As Range, temp
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        .Font.ColorIndex = xlAutomatic
        For Each r In .Cells
            If r <> temp Then r.Font.Color = vbRed: temp = r
        Next
    End With
End Sub

Thank you. I asked the previous responder if we could act on entire row rather than just a single cell.

 

[Fuji]

You mean like this?

Sub test()
    Dim r As Range, temp
    With [a1].CurrentRegion
        .Font.ColorIndex = xlAutomatic
        For Each r In .Columns(5).Offset(1).Resize(.Rows.Count - 1).Cells
            If r <> temp Then .Rows(r.Row - .Row + 1).Font.Color = vbRed: temp = r
        Next
    End With
End Sub

 

[gtd526]

You mean like this?

Sub test()
    Dim r As Range, temp
    With [a1].CurrentRegion
        .Font.ColorIndex = xlAutomatic
        For Each r In .Columns(5).Offset(1).Resize(.Rows.Count - 1).Cells
            If r <> temp Then .Rows(r.Row - .Row + 1).Font.Color = vbRed: temp = r
        Next
    End With
End Sub

Yes. I adjusted the Column(5) to (4) so its looking at the column for the change. Thank you.

Book1
	A	B	C	D	E
1	Header1	Header2	Header3	Header4	Header5
2	ORL	7	no	1	same
3	MIL	-7	no	1	diff
4	DAL	4	yes	1	diff
5	OKC	-4	no	2	diff
6	ATL	7.5	no	2	same
7	NYK	-7.5	yes	2	same
8	GSW	2	yes	3	same
9	HOU	-2	yes	3	diff
10	DET	13.5	yes	3	diff
11	BOS	-13.5	no	3	diff
12	TOR	11	no	4	diff
13	MIA	-11	no	4	diff
14	SAC	-5.5	yes	4	same
15	NOP	5.5	yes	4	same
Sheet1

 

[Fuji]

The column to look for the change is "D", when that value changes.

Oops... and glad you fixed it.

 

[Skyybot]

Avoid use of On Error statement,

Sub test()
    Dim r As Range, temp
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        .Font.ColorIndex = xlAutomatic
        For Each r In .Cells
            If r <> temp Then r.Font.Color = vbRed: temp = r
        Next
    End With
End Sub

@Fuji , I agree but had to in this case because there is no 0 row. I could've thrown an If statement in there, but I was feeling lazy.