Get cell formatting

Question

Is there a function to get the activecell formatting? e.g. background color, font, font color, cell border, font size etc.

I want to update the format of an entire worksheet based on a formatted cell before action (i.e. the format I want to change) by another formatted cell (i.e. the format I want to apply).

Sub Rep_all_format()
Dim fmt_bef As CellFormat
Dim fmt_aft As CellFormat
Dim rngReplace As Boolean
Dim msg As String
Dim Sh As Worksheet
Dim Rg As Range
Dim ppos1 As Range
Dim ppos2 As Range
Dim Find As String
Dim Remplace As String
Set ppos1 = Application.InputBox(Prompt:="Select the cell format you wanna change", Title:="Remplace", Default:=ActiveCell.Address, Type:=8)
Set ppos2 = Application.InputBox(Prompt:="Select the cell format you wanna apply", Title:="Select", Type:=8)
    Find = ppos1.FormatConditions 'this is theorical I do not know the function
    Remplace = ppos2.FormatConditions 'this is theorical I do not know the function
Application.ScreenUpdating = False
Set fmt_bef = Application.FindFormat
Set fmt_aft = Application.ReplaceFormat
For Each Sh In ThisWorkbook.Worksheets
    Set Rg = Sh.UsedRange
    With fmt_bef
        .Clear
        .FormatConditions = Find
    End With
    With fmt_aft
        .Clear
        .FormatConditions = Remplace
    End With
    Rg.Replace What:="", Replacement:="", _
    SearchFormat:=True, ReplaceFormat:=True
Next
fmt_bef.Clear
fmt_aft.Clear
Application.ScreenUpdating = True
MsgBox ("The desired format has been applied through all the workbook")
End Sub

Answer 1

Assuming, from the code that you have provided, that your cell has been formatted using Conditional Formatting, you need to access is the Range.DisplayFormat property.

Note that I showed only some of the formatting options for a cell. There is documentation online for other formatting options (eg other borders, numberformat, etc) but this should get you started.

For example:

Option Explicit
Sub foo()
    Dim R As Range, C As Range
    Dim fc As FormatCondition

Set R = Range(Cells(1, 1), Cells(5, 1))

For Each C In R
    With C.DisplayFormat
        Debug.Print .Interior.Color
        Debug.Print .Font.Name
        Debug.Print .Font.Color
        Debug.Print .Borders(xlEdgeLeft).LineStyle ' etc
        Debug.Print .Font.Size
    End With
    Stop
Next C

End Sub

If the cell has been formatted manually, or directly using code, then just access the various properties directly, not using the DisplayFormat property eg:

For Each C In R
    With C
        Debug.Print .Interior.Color
        Debug.Print .Font.Name
        Debug.Print .Font.Color
        Debug.Print .Borders(xlEdgeLeft).LineStyle ' etc
        Debug.Print .Font.Size
    End With
    Stop
Next C