Macro that copy selection of cells before changing font color

[MrMyagiii]

Hi all,

I`m starting to learn about macro`s and VBA.

Currently i`m using this macro to change the font color from the selection of cells :

Sub Kleur_Copy()

    Dim rngCell As Range
    
    Application.ScreenUpdating = False
    
    For Each rngCell In Selection
    
        rngCell.Font.Color = RGB(0, 165, 0)
    Next rngCell
    
    Application.ScreenUpdating = True
    
End Sub

Now i want to add Copy.Selection to this so that it saves the selection onto the clipboard, however i can`t get it to copy without the changed font color. I know i can fix this with pasting values only, but is there a way to copy the values before changing font color?

Kind regards,

[AlphaFrog]

Put Selection.Copy as the first line of the procedure before you change the font color.

[MrMyagiii]

You mean like this?
I tried this before but still gives the same result. The pasted text has changed font color. I dont understand why

Sub Kleur_Copy()

Copy.Selection

Dim rngCell As Range

Application.ScreenUpdating = False

For Each rngCell In Selection

rngCell.Font.Color = RGB(0, 165, 0)
Next rngCell

Application.ScreenUpdating = True

End Sub

[AlphaFrog]

You mean like this?
I tried this before but still gives the same result. The pasted text has changed font color. I dont understand why

Sub Kleur_Copy()

Copy.Selection

Dim rngCell As Range

Application.ScreenUpdating = False

For Each rngCell In Selection

rngCell.Font.Color = RGB(0, 165, 0)
Next rngCell

Application.ScreenUpdating = True

End Sub

The command is Selection.Copy (not Copy.Selection)

[MrMyagiii]

Yeah i know, my bad. But still doesn`t work with :

Sub Kleur_Copy()

Dim rngCell As Range

Application.ScreenUpdating = False

Selection.Copy

For Each rngCell In Selection

rngCell.Font.Color = RGB(0, 165, 0)
Next rngCell

Application.ScreenUpdating = True

End Sub

The pasted content still returns with the "new" font color

[AlphaFrog]

The pasted content still returns with the "new" font color

What are you doing to paste the content? If you're using a macro to paste, have it remove the font color as well.

[rajansettu]

Hi,

Try below one

Sub Kleur_Copy()

Dim rngCell As Range

Application.ScreenUpdating = False

Selection.Copy

For Each rngCell In Selection

rngCell.Font.Color = RGB(0, 165, 0)
Next rngCell

Application.ScreenUpdating = True

End Sub

[Marc L]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[AlphaFrog]

You can Paste Special without a macro
On the menu: Edit\PasteSpecial\Values

[MrMyagiii]

Ok so i have to look for a solution in the "paste" direction. It is not possible to copy without changed font color?

[rajansettu]

Hi,


Ii works pls check the attached sheet and below code. maybe you have selected default font color as green try after changing it into black.

Sub Kleur_Copy()
Dim rngCell As Range
Application.ScreenUpdating = False
ActiveCell.CurrentRegion.Select
Selection.Copy
For Each rngCell In Selection
rngCell.Font.Color = RGB(0, 165, 0)
Next rngCell
Application.ScreenUpdating = True
Range("a1").Select
Selection.PasteSpecial (xlPasteValues)
End Sub

[rajansettu]

Hi,

try this one

[code]

Sub Kleur_Copy()
Dim rngCell As Range
Application.ScreenUpdating = False
ActiveCell.CurrentRegion.Select
Selection.Copy
Workbooks.Add
Windows("Book1").Activate
Selection.PasteSpecial (xlPasteValues)
ThisWorkbook.Activate
For Each rngCell In Selection
rngCell.Font.Color = RGB(0, 165, 0)
Next rngCell
Application.ScreenUpdating = True
End Sub

[\code]

[MrMyagiii]

Gives an error on :
ActiveCell.Currentregion.select

The worksheet is protected, however the cells that need to be copied and changed are available