pasting font color from conditional format to permanent

[henway]

Sounds simple.
I have backwards co-workers that can't figure out how/why to use conditional formatting and have deigned it "not acceptable".

They've requested I fill in a HUGE spreadsheet and change the font color to red if it meets a criteria.

Simple enough to do with a conditional format that compares my data.

HOWEVER, now I need the individual cells to stay red and remove the conditional format (because conditional formatting "confuses" them). Removing the condition causes the color to go back to black. I've tried all sorts of paste specials (formula/format) to no avail...

Any ideas on how to do this?

 

[lenze]

Let's say your criteria is cell >10. So something like this

Sub ConvertCF()
Dim cl As Range
For Each cl In Range("$A$2:$A" & Range("$A$65536").End(xlUp).Row)
If cl > 10 Then cl.Font.ColorIndex = 3
Next cl
End Sub

Post back your specifics

lenze

 

[henway]

Thank you--I'll try it. I shy away from VBA, but I'll give it a whirl and see what happens.

 

[lenze]

You might want to change it slightly

Sub ConvertCF()
Dim cl As Range
For Each cl In Range("$A$2:$A" & Range("$A$65536").End(xlUp).Row)
If cl > 10 Then 
     cl.Font.ColorIndex = 3
Else: cl.Font.ColorIndex = xlAutomatic
End If
Next cl
End Sub

lenze

 

[henway]

Thank you--I tried it but I don't think I properly explained the problem.

For conditional formatting, I have column J looking at column U and turning red if each cell in the column is less than U.

It looks perfect.

However, I need to somehow remove the conditional formatting and have the cells that were red remain so, and the other ones (black) stay black.

I don't have a global cell to be greater or smaller than. Column J has lots of lines---autofilter for [Red] doesn't work, either...

 

[lenze]

Sub Convert CF()
Dim cl as Range
For Each cl In Range("$J$2:$J" & Range("$J$65536").End(xlUp).Row)
If cl < Cells(cl.Row,"U") Then
     cl.Font.ColorIndex = 3
Else:Font.ColorIndex = xlAutomatic
End If
Next cl
End Sub

lenze

 

[henway]

I've gotten stuck in VBA. I tried to make a module for this and it won't save it and can't find it when I try to run it, nor is "Create" highlighted. First time with VBA--sorry.......

Any ideas OUTSIDE of VBA?

 

[parry]

Simply use the AutoFilter and for the column in question and in the custom option put in a formula that meets your current condition format. Now that you have these cells filtered, select the column and apply the formatting you need (ie red). Now you can just remove the conditional format and these cells will remain red.

You should point out to your colleagues that c/f will amend the format automatically, so if they change any values then the font of course wont change to red so on their head be it!

cheers,
Graham

 

[henway]

Thanks, but I don't think your solution works.

For each cell in J, the conditional format is:
If cell value is greater than J*0.9
then format the font to be RED

The filter can only look for absolutes, not relatives.

 

[Jonmo1]

See here

http://www.vbaexpress.com/kb/getarticle.php?kb_id=190

Hope that will help..