pasting font colour from conditional formatting to permanent in excel

route217
route217 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
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.
Ryan ChongSoftware Team Lead
Commented:
try this:

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

Open in new window

Microsoft Excel Expert
Top Expert 2014
Commented:
route217

i have uploaded an example for you.

range A1toL20 has conditional formatting.

then you run the macro by pressign control shift B and it it will prompt to select the range of conditional formatting.  select the A1:L20 then click ok and it will delete conditional formatting and replace the color of conditional formatting to the background formatting.
EEE.xlsb
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Ryan ChongSoftware Team Lead

Commented:
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

Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
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.
route217Junior

Author

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

Both methods work fine

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial