PDA

View Full Version : copy a color of font and cell of conditionally fomatted cell



hunna
12-04-2011, 01:48 PM
Hello, does anybody know how to copy a color of font and cell of conditionally fomatted cell?

thanks,

Kenneth Hobs
12-04-2011, 04:06 PM
See Chip Pearson's code on his site. It does not work for all cases. The better method is to look at the conditions that cause the colors.

Aflatoon
12-05-2011, 01:59 AM
If you are using 2010 you can use the Displayformat property of the cell; if not, see Kenneth's answer.

hunna
12-05-2011, 02:25 AM
Thanks for answers, I will have a look at the sites.

hunna
12-05-2011, 02:46 AM
@ Aflatoon (http://www.vbaexpress.com/forum/member.php?u=24778)

How to use the Displayformat property of the cell, excel 2010? can you give me some example?

thanks,

Aflatoon
12-05-2011, 03:11 AM
It would be something similar to this:
Sub MakeCFStatic()
Dim rgCell As Excel.Range

For Each rgCell In Selection
If rgCell.FormatConditions.Count > 0 Then
With rgCell.DisplayFormat
rgCell.Font.Bold = .Font.Bold
rgCell.Font.Italic = .Font.Italic
rgCell.Font.Color = .Font.Color
Select Case .Interior.ColorIndex
Case xlColorIndexAutomatic, xlColorIndexNone
rgCell.Interior.ColorIndex = .Interior.ColorIndex
Case Else
rgCell.Interior.Color = .Interior.Color
End Select
End With
rgCell.FormatConditions.Delete
End If
Next rgCell
End Sub


depending on which properties you were interested in.

Kenneth Hobs
12-05-2011, 07:02 AM
For the active colors in A1:
Sub t()
MsgBox "Font Color: " & Range("A1").DisplayFormat.Font.Color & vbLf & _
"Interior Color: " & Range("A1").DisplayFormat.Interior.Color, vbInformation, "A1"
End Sub

hunna
12-05-2011, 08:27 AM
Aflatoon (http://www.vbaexpress.com/forum/member.php?u=24778) and Kenneth Hobs (http://www.vbaexpress.com/forum/member.php?u=3661)

Thanks for the codes. :)