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]

Brilliant @JoeMo !!! ??
Works Perfect!!! ??
Thank you very much my Friend. ????

You are welcome - thanks for the reply.

 

[Lacan]

Dear @JoeMo

First, hope this post can find you well!

Please would like an update of your VBA Formula.
Would like that formula worked with conditional formatting inserted in table despite of the colour chosen in CF would like that returned to the parameterized colour in Formula VBA Code.
Just put first the VBA Formula and then the sheet with table.
Hope you can help?
Any questions please let know.
Thank you very much my friend!!!

Private Sub Worksheet_Change(ByVal Target As Range)
Const colorOdd As Long = vbBlue
Const colorEven As Long = vbBlack
Static N() As Long
Dim R As Range, c As Range  'R will be the range you want to monitor for changes
Set R = Range("E4:E12")
ReDim Preserve N(1 To R.Rows.Count)
If Not Intersect(Target, R) Is Nothing Then
    For Each c In Intersect(Target, R)
        N(c.Row - R(1).Row + 1) = N(c.Row - R(1).Row + 1) + 1
        With Range("C" & c.Row, "F" & c.Row)
            If N(c.Row - R(1).Row + 1) Mod 2 = 0 Then
                With .Font
                    .Bold = False
                    .Color = colorEven
                End With
            Else
                With .Font
                    .Bold = True
                    .Color = colorOdd
                End With
            End If
        End With
    Next c
End If
End Sub

Rewrite_06-05-24.xlsm
	A	B	C	D	E	F	G
1
2				01/01/2024
3			NAME	DATE	QTD	PVP
4			A	01/12/2023	2	5 €
5			B	02/05/2024	3	8 €
6			C	15/10/2023	6	3 €
7			D	04/03/2024	2	10 €
8			E	04/04/2024	2	30 €
9			F	01/05/2024	5	14 €
10			G	10/11/2023	21	21 €
11			H	30/09/2023		9 €
12			I	03/03/2024	22	11 €
13
14
15
Folha1

 

[JoeMo]

Would like that formula worked with conditional formatting inserted in table despite of the colour chosen in CF would like that returned to the parameterized colour in Formula VBA Code.
Just put first the VBA Formula and then the sheet with table.

I don't understand what you want. In the data that you show in post #22, which cells have CF? If you want the CF color changed even when the CF condition is met then CF must be removed prior to changing the color. Is that what you want?

 

[Lacan]

I don't understand what you want. In the data that you show in post #22, which cells have CF? If you want the CF color changed even when the CF condition is met then CF must be removed prior to changing the color. Is that what you want?

Hi @JoeMo
In the data that you show in post #22, which cells have CF? Accordingly with formula "=SE($D4<$D$2;1)", CF is applied to all table.

If you want the CF color changed even when the CF condition is met then CF must be removed prior to changing the color. Is that what you want?
Yes, would like to change the colour of CF to the colour of the VBA Formula "Blue" after write in Column "QTD" even if CF is met. If you test it, like the same in all other cells wich CF is not met. ( Like example in "E7", after write in it change to Blue and Bold )
Because this is a small example of an Order List is quite difficult in front of the customer to remove CF cell by cell when it is applied.
Thought that VBA would help in being more easier and fast.
If you need an example please let me know can make in another table side by side the goal results.
Thank you very much.
Here is the table.

Rewrite_06-05-24.xlsm
	A	B	C	D	E	F	G
1
2				01/01/2024
3			NAME	DATE	QTD	PVP
4			A	01/12/2023	2	5 €
5			B	02/05/2024	3	8 €
6			C	15/10/2023	6	3 €
7			D	04/03/2024	5	10 €
8			E	04/04/2024	2	30 €
9			F	01/05/2024	5	14 €
10			G	10/11/2023	21	21 €
11			H	30/09/2023	3	9 €
12			I	03/03/2024	22	11 €
13
14
Folha1

 

[JoeMo]

If you need an example please let me know can make in another table side by side the goal results.

Yes, please give me side by side version. And please include all the CF information that XL2BB can provide for the starting table.

 

[Lacan]

Yes, please give me side by side version. And please include all the CF information that XL2BB can provide for the starting table.

Dear @JoeMo

Accordingly keeping your VBA Formula here is the goals that would like to achieve with the Examples in Table 2 and Table 3 keeping the CF on.
To explain and show the results pretended and keep it KISS just made changes in cells I5, I9 and I12 and then N5, N9 and N12 and so on.
Please let me know it need more information.
Thank you very much!!!

Rewrite_06-05-24.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2		STARTING TABLE					RESULTS AFTER 1ST CHANGE					RESULTS AFTER 2ND CHANGE
3			01/01/2024					01/01/2024					01/01/2024
4		NAME	DATE	QTD	PVP		NAME	DATE	QTD	PVP		NAME	DATE	QTD	PVP
5		A	01/12/2023	1	5 €		A	01/12/2023	2	5 €		A	01/12/2023	3	5 €
6		B	02/05/2024	1	8 €		B	02/05/2024	1	8 €		B	02/05/2024	1	8 €
7		C	15/10/2023	1	3 €		C	15/10/2023	1	3 €		C	15/10/2023	1	3 €
8		D	04/03/2024	1	10 €		D	04/03/2024	1	10 €		D	04/03/2024	1	10 €
9		E	04/04/2024	1	30 €		E	04/04/2024	2	30 €		E	04/04/2024	3	30 €
10		F	01/05/2024	1	14 €		F	01/05/2024	1	14 €		F	01/05/2024	1	14 €
11		G	10/11/2023	1	21 €		G	10/11/2023	1	21 €		G	10/11/2023	1	21 €
12		H	30/09/2023	1	9 €		H	30/09/2023	2	9 €		H	30/09/2023	3	9 €
13		I	03/03/2024	1	11 €		I	03/03/2024	1	11 €		I	03/03/2024	1	11 €
14
Folha1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:E13	Expression	=SE($C5<$C$3;1)	text	NO

 

[JoeMo]

Accordingly keeping your VBA Formula here is the goals that would like to achieve with the Examples in Table 2 and Table 3 keeping the CF on.
To explain and show the results pretended and keep it KISS just made changes in cells I5, I9 and I12 and then N5, N9 and N12 and so on.

The use of CF with a function I have never seen before (SE) and do not have available on MS 365 US version complicates things enough that I think you should start a new thread with your request.
I don't know what you mean by "keeping the CF on". I don't think you can programmatically change the font color of a CF cell having a CF expression that evaluates to True unless you first remove the CF (i.e. delete the format condition).

 

[Lacan]

Dear @JoeMo 🖐

In order to help and clarify (SE) translated to US is equal to the IF function.
Yes, was thinking that would be possible to compatible both in other words through CF add one colour and VBA programmatically change the font color to a different one.
Will think if should start a new thread.
Again, thank you very much for your care and concern.
Will keep in touch.

 

[JoeMo]

Dear @JoeMo 🖐

In order to help and clarify (SE) translated to US is equal to the IF function.
Yes, was thinking that would be possible to compatible both in other words through CF add one colour and VBA programmatically change the font color to a different one.
Will think if should start a new thread.
Again, thank you very much for your care and concern.
Will keep in touch.

You are welcome - thanks for the reply.