How to change text color to different color after rewrite cell?

[Lacan]

Hello Guys,

Using VBA how can change text color to different color after rewrite text cell?

Thank you very much for the help.

 

[JoeMo]

Hello Guys,

Using VBA how can change text color to different color after rewrite text cell?

Thank you very much for the help.

Your post is short on detail so this is just an example of code for a worksheet that will change font color on cell rewrites for the range A1:A10 of the worksheet. I used red and black to alternate on rewrites. You can change the colors and the range to meet your needs. Rewrites can be by manual typing or by pasting to one or more cells in the target range.

Private Sub Worksheet_Change(ByVal Target As Range)
Const color1 As Long = vbBlack
Const color2 As Long = vbRed
Dim c As Range
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then  'Change range to suit
    For Each c In Intersect(Target, Range("A1:A10"))
        If Not IsEmpty(c) Then
            If c.Font.Color = color1 Then
                c.Font.Color = color2
            ElseIf c.Font.Color = color2 Then
                c.Font.Color = color1
            End If
        End If
    Next c
End If
End Sub

 

[Lacan]

Your post is short on detail so this is just an example of code for a worksheet that will change font color on cell rewrites for the range A1:A10 of the worksheet. I used red and black to alternate on rewrites. You can change the colors and the range to meet your needs. Rewrites can be by manual typing or by pasting to one or more cells in the target range.

Private Sub Worksheet_Change(ByVal Target As Range)
Const color1 As Long = vbBlack
Const color2 As Long = vbRed
Dim c As Range
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then  'Change range to suit
    For Each c In Intersect(Target, Range("A1:A10"))
        If Not IsEmpty(c) Then
            If c.Font.Color = color1 Then
                c.Font.Color = color2
            ElseIf c.Font.Color = color2 Then
                c.Font.Color = color1
            End If
        End If
    Next c
End If
End Sub

Works perfect my friend!

Just one improvement detail - after change it to colour red how can turn into format bold text at the same time (only in red colour not in black)?

Thanks again. ??

 

[JoeMo]

Works perfect my friend!

Just one improvement detail - after change it to colour red how can turn into format bold text at the same time (only in red colour not in black)?

Thanks again. ??

You are welcome - thanks for the reply.
For your new request, replace the code I gave you with this revised version.

Private Sub Worksheet_Change(ByVal Target As Range)
Const color1 As Long = vbBlack
Const color2 As Long = vbRed
Dim c As Range
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then  'Change range to suit
    For Each c In Intersect(Target, Range("A1:A10"))
        If Not IsEmpty(c) Then
            If c.Font.Color = color1 Then
                With c.Font
                    .Color = color2
                    .Bold = True
                End With
            ElseIf c.Font.Color = color2 Then
                With c.Font
                    .Color = color1
                    .Bold = False
                End With
            End If
        End If
    Next c
End If
End Sub

 

[Lacan]

You are welcome - thanks for the reply.
For your new request, replace the code I gave you with this revised version.

Private Sub Worksheet_Change(ByVal Target As Range)
Const color1 As Long = vbBlack
Const color2 As Long = vbRed
Dim c As Range
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then  'Change range to suit
    For Each c In Intersect(Target, Range("A1:A10"))
        If Not IsEmpty(c) Then
            If c.Font.Color = color1 Then
                With c.Font
                    .Color = color2
                    .Bold = True
                End With
            ElseIf c.Font.Color = color2 Then
                With c.Font
                    .Color = color1
                    .Bold = False
                End With
            End If
        End If
    Next c
End If
End Sub

Works Percfectly.
Thank you very much.

 

[JoeMo]

Works Percfectly.
Thank you very much.

You are welcome - thanks for the reply.

 

[Lacan]

You are welcome - thanks for the reply.

Dear @JoeMo, ??

Hope you are fine. ?

Im thinking to make an update in your excellent formula.

Using
Const color1 As Long = vbBlack
Const color2 As Long = vbRed

In 1st write imagine you write something in cell A1 how can highlight "Const color1 As Long = vbBlack" text colour in range "A1:D1"?
In 2st write in cell A1 highlight "Const color2 As Long = vbRed" text colour in range "A1:D1"?

Hope Im make myself clear.

Thank you very much.????

 

[JoeMo]

Need some clarification.
You want the text to be bold whenever the font color is red and not bold when color is black?
What do you want to happen when writing to A1 anytime after the second write to that cell?

 

[Lacan]

Need some clarification.
You want the text to be bold whenever the font color is red and not bold when color is black?
What do you want to happen when writing to A1 anytime after the second write to that cell?

Dear @JoeMo

To clarify give an example in table below:

When make the first write in quantity in cell "H1" want all range from F5:I5 with format text standard automatic.
After make the second change in quantity in cell "H1" want all range from F5:I5 with format text bold colour text blue 255 #0000FF.
And make the third change in quantity in cell "H1"want to return again all range from F5:I5 with format text standard automatic.
And so on.

Thank you very much @JoeMo ????.

Livro1.xlsx
	D	E	F	G	H	I	J	K
3
4			N.º ITEM	PRODUT	QUANTITY	PVP
5		1ST WRITE	235	TV	1	5 €
6		2ND WRITE	235	TV	1	5 €
7		3RD WRITE	235	TV	1	5
8
9
Folha3

 

[JoeMo]

Would it be safe to say if N is the number of writes to H1, then the font is standard (default) when N is even and bold with blue 255#0000FF color when N is odd?