How do I change the font color in a range of cells to the same font color in another range of cells using a vba.

[Patches01]

I want to change the font color in a range of cells to the same font color in another range of cells using a vba.

I have the below code however it only seems to work with one cell to another cell not a range of cells.


Me.Range("g4").Font.ColorIndex = Me.Range("f4").Font.ColorIndex

Thank you

 

[bobsan42]

Me.Range("g4:n20").Font.ColorIndex = Me.Range("f4").Font.ColorIndex

this is if all the cells in the range share the same font color. And of course change the range to whatever you need.

 

[Patches01]

Me.Range("g4:n20").Font.ColorIndex = Me.Range("f4").Font.ColorIndex

this is if all the cells in the range share the same font color. And of course change the range to whatever you need.

Some cells in the range are blank and the font color changes depending on the entered text for example less than a zero changes to red font and more than zero changes to black font. The above code did not work.

 

[jkpieterse]

In that case you do not need to change the Font color, but rather the NumberFormat:

Me.Range("g4:n20").NumberFormat = Me.Range("f4").NumberFormat

 

[bobsan42]

The code works, it just does not do what you need, because it has to be done in a different way. The color index of the basic cell is obviously not changed manually. Provide some sample data and explain in detail what exactly you need. Also explain how the number colors are changed - Conditional formatting or Custom number format.

 

[bobsan42]

In that case you do not need to change the Font color, but rather the NumberFormat:

Me.Range("g4:n20").NumberFormat = Me.Range("f4").NumberFormat

This may not work if the values in the target range are not numbers.
Conditional formatting may be a better option than vba.

 

[jkpieterse]

This may not work if the values in the target range are not numbers.
Conditional formatting may be a better option than vba.

I assumed the numberformat *is* using conditions to color the font and that numberformat has already been applied to cell F4.

 

[bobsan42]

If F4 contains numbers and the number format works on it, it will not work on g4 if it contains text.

 

[Patches01]

The code works, it just does not do what you need, because it has to be done in a different way. The color index of the basic cell is obviously not changed manually. Provide some sample data and explain in detail what exactly you need. Also explain how the number colors are changed - Conditional formatting or Custom number format.

I apologise. I a new to VBN coding. To be more clear.
I have a sheet that is used for balancing deposits paid and refunded the following information is used.
Date, Name, Money paid or refunded and the initials of the staff member entering the information.
For Example
A4 = DATE, B4 = NAME, C4, D4.E4 AND F4 = THE DOLLOR AMOUNT PAID OR REFUNDED, G4 = THE INITIALS OF THE STAFF MEMBER
When the dollar amount is entered I have a code for the amount to change font color to RED if it is a refund to a customer and to stay BLACK if it is a payment to the business.
I require A4, B4 and G4 to change RED when a REFUND occurs.
Here is the code as I have it so far.
Private Sub CommandButton2_Click()

Dim myRange As Range
Set myRange = Range("c4:f42")

For Each Cell In myRange
If Cell.Value < 0 Then Cell.Font.ColorIndex = 3


Set myRange = Range("c4:f42")


If Cell.Value > 0 Then Cell.Font.ColorIndex = 1


Next

Thank you for your help

 

[Patches01]

In that case you do not need to change the Font color, but rather the NumberFormat:

Me.Range("g4:n20").NumberFormat = Me.Range("f4").NumberFormat

I have a sheet that is used for balancing deposits paid and refunded the following information is used.
Date, Name, Money paid or refunded and the initials of the staff member entering the information.
For Example
A4 = DATE, B4 = NAME, C4, D4.E4 AND F4 = THE DOLLOR AMOUNT PAID OR REFUNDED, G4 = THE INITIALS OF THE STAFF MEMBER
When the dollar amount is entered I have a code for the amount to change font color to RED if it is a refund to a customer and to stay BLACK if it is a payment to the business.
I require A4, B4 and G4 to change RED when a REFUND occurs.
Here is the code as I have it so far.
Private Sub CommandButton2_Click()

Dim myRange As Range
Set myRange = Range("c4:f42")

For Each Cell In myRange
If Cell.Value < 0 Then Cell.Font.ColorIndex = 3


Set myRange = Range("c4:f42")