Find based on cond. 4matting? Replace as ALT-Enter? (2 diff prob

[Guest]

1- I've used Cond 4Mat to color a whole bunch of cells in my sheet. I now
what to do a Find/Replace based on Finding the different colored cells, which
I can't do, because Find is color-blind to Cond4Mat colors.
When data's generated by formulas in cells, I can copy & paste text to get
rid of the formulas. I'd like to keep the color & get rid of the Cond4Matting
once it's been executed. Any way?

2- I can find or replace with ^l, ^p, ^t, etc., etc., which ability has been
quite powerful for me. I have not, however, found a way of finding or
replacing with EXCEL's NewLine entry of choice: ALT-ENTER. Can anyone supply
me with that magic pill so I can get my power back?

-Cate

 

[Bob I]

Press Alt and key in 0010 on the keypad.

 

[Guest]

Awesome, Bob I. When I think of the years I've puzzled over this!!!!!!!
Is it too greedy of me to hope you also have an answer for my 1st issue --
leaving the formatting, while removing the conditional formatting formulas
from cells?
-Cate

 

[Bob I]

To remove all Conditional formatting, just select area of cells, click
Format, Conditional Formatting.. , Delete, tick 1 2 and 3 and OK, OK.

 

[Gord Dibben]

1. Chip's Pearson's site has UDF's to return the CF colors so's you can find
them

http://www.cpearson.com/excel/CFColors.htm

2. Alt + 0010 on Numpad or use CTRL + j


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Bob

This will not aid in "leaving the formatting"?

OP must first, manually or via VBA, color the cells to the CF format before
deleting the CF conditions.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

If you're converting the formulas to values--so you don't care about the
formulas, you may want to try this.

Open a new MSWord document (yep, MSWord)
Select the range to copy in excel
edit|copy
Paste into MSWord
Remove the format|Conditional formatting from that range
copy and paste from MSWord back into excel.

If the range is large, you may have to do this in bite sized pieces.

Heck, if the range is small, you may just want to keep it in MSWord?????

 

[Bob I]

Sorry, I understood to not change any of the cell formatting, not the
results of the Conditional formatting.