时间戳

首页 > 解决方案 > 获取单元格格式

excel - 获取单元格格式

问题描述

是否有获取活动单元格格式的功能？例如背景颜色、字体、字体颜色、单元格边框、字体大小等。

我想通过另一个格式化单元格（即我要应用的格式）在操作之前根据一个格式化单元格（即我要更改的格式）更新整个工作表的格式。

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

标签： excelvbaformattingcell

解决方案

假设从您提供的代码中，您的单元格已使用条件格式进行格式化，您需要访问的是Range.DisplayFormat属性。

请注意，我只显示了单元格的一些格式选项。有其他格式选项（例如其他边框、数字格式等）的在线文档，但这应该可以帮助您入门。

例如：

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

如果单元格已手动或直接使用代码格式化，则只需直接访问各种属性，而不是使用DisplayFormat属性，例如：

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

推荐阅读