Remove Conditional Formatting but keep format

sgg03246

New Member
Joined
Sep 3, 2008
Messages
5
Hi there,
Does anyone knows how i can remove Conditional Formatting but keep the format which has been done via conditional format?

Thanks.
 
I know if you paste the data into power point keeping the source formatting then copy and paste it back it should lose the conditions.

I guess it depends on how many rows and columns you're talking about as to whether this is practical or not.

Usually my data sets are pretty manageable so its not a big deal.
 
Upvote 0

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
I know if you paste the data into power point keeping the source formatting then copy and paste it back it should lose the conditions.

I guess it depends on how many rows and columns you're talking about as to whether this is practical or not.

Usually my data sets are pretty manageable so its not a big deal.



This idea worked! I just pasted it into an outlook email and then back into Excel 2010! Thanks!!!!
 
Upvote 0
Quicker way for me at any rate (YMMV)

Code:
add sort column
fill series 1 to EoS (End of Sheet)
for each of the conditional formats
       sort sheet/range by that format
       select a conditionally formatted cell of interest
       hit 'Format Painter' and paint the desired cells accordingly
next
sort sheet/range by sort column
delete sort column
I did add an original sort column at the start, and then sort by the formatted cells of interest, format painted, and then resorted by the sort column and then deleted the sort column
 
Last edited:
Upvote 0
I realized that I have to break the problem down to find resolution. Here is what I have thus far. I used standard conditional formatting for cell ranges B4:C10, E4:E10, B13, C17, E13:E17 I was wondering if it is possible to keep the shading that was produced by the standard conditional formatting, but remove the data. have the following code, it ran once deleted the data, & then hung up when I tried to run it a second time. I modified the code from one I found on line.
Code:
rivate Sub Worksheet_SelectionChange(ByVal Target As Range)    Dim ws As Worksheet
    Dim mySel As Range, aCell As Range


    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("October_Meeting_#_1")
    '~~> Change this to the relevant range
    Set mySel = ws.Range("b41:c10")


    For Each aCell In mySel
        With aCell
          .Font.FontStyle = .DisplayFormat.Font.FontStyle
          .Interior.Color = .DisplayFormat.Interior.Color
          .Font.Strikethrough = .DisplayFormat.Font.Strikethrough
        End With
    Next aCell
End Sub
I can not hide the date, because it would provide incorrect answers later in the workbook. Any Ideas? Also, when I ran it a second time it hung up on Set ws = ThisWorkbook.Sheets("October_Meeting_#_1. I tried using the sheet 3, , but it still hangs on that line.
 
Upvote 0

Forum statistics

Threads
1,212,932
Messages
6,110,748
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top