Link to home
Start Free TrialLog in
Avatar of route217
route217Flag for United Kingdom of Great Britain and Northern Ireland

asked on

pasting font colour from conditional formatting to permanent in excel

Hi experts using excel 2010

I want to paste the conditional formatting colours from the CF rules to permanent colour in excel..how could I do this using vba or another method..

Data range is q11:AA200
Avatar of Professor J
Professor J
easy way is to Copy/paste to Word and back to Excel appears to convert any kind (font, border, pattern) of conditional format to regular explicit format.
SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of Professor J
Professor J
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image
opps, repost:

Sub test()
    Dim r As Range, c As Range
    
    Set r = Range("A1:A10")
    
    For Each c In r
        c.Interior.Color = c.DisplayFormat.Interior.Color
    Next
    r.FormatConditions.Delete
End Sub

Open in new window

Avatar of Professor J
Professor J
the solution i uploaded, takes care of all formatting from conditional formatting not just the color. i.e Font Style, Underline and Strikethrough Font Color etc.
Avatar of route217
route217
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Thanks for the excellent feedback...all experts..

Both methods work fine