本文介绍了使用VBA Excel功能获取背景颜色的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
公式应该返回指向单元格作为参数的背景颜色。例如 = BackGroundColor(C3)
应该返回单元格 C3
的背景颜色。
The formula should return the background color of a cell pointed as argument. For example =BackGroundColor(C3)
should return background color of cell C3
.
Public Function BackGroundColor(rng As Range)
BackGroundColor = rng.DisplayFormat.Interior.Color
End Function
尝试使用 rng.Address
他们都没有工作。你可以建议我做错什么吗?
Tried different alternatives with rng.Address
etc. Neither of them worked. Can you please suggest what I am doing wrong?
推荐答案
只需放下DisplayFormat,
Just drop the DisplayFormat,
Public Function BackGroundColor(rng As Range)
BackGroundColor = rng.Interior.Color
End Function
这应该会在长
中给你颜色值可悲的是,上述函数不会返回条件格式化颜色。这是一种方法,我在网上找到另一个。代码是
It is sadly true the above function does not return for Conditional Formatting colours. Here is a method, I found online on another Forum. The code is,
' Arguments
' ----------------
' Cell - Required Range, not a String value, for a **single** cell
'
' CellInterior - Optional Boolean (Default = True)
' True makes function return cell's Interior Color or ColorIndex based on
' the ReturnColorIndex argument False makes function return Font's Color or
' ColorIndex based on the ReturnColorIndex argument
'
' ReturnColorIndex - Optional Boolean (Default = True)
' True makes function return the ColorIndex for the cell property determined
' by the CellInterior argument False make function return the Color for the
' cell property determined by the CellInterior argument
'
Function DisplayedColor(Cell As Range, Optional CellInterior As Boolean = True, _
Optional ReturnColorIndex As Long = True) As Long
Dim X As Long, Test As Boolean, CurrentCell As String
If Cell.Count > 1 Then Err.Raise vbObjectError - 999, , "Only single cell references allowed for 1st argument."
CurrentCell = ActiveCell.Address
For X = 1 To Cell.FormatConditions.Count
With Cell.FormatConditions(X)
If .Type = xlCellValue Then
Select Case .Operator
Case xlBetween: Test = Cell.Value >= Evaluate(.Formula1) And Cell.Value <= Evaluate(.Formula2)
Case xlNotBetween: Test = Cell.Value <= Evaluate(.Formula1) Or Cell.Value >= Evaluate(.Formula2)
Case xlEqual: Test = Evaluate(.Formula1) = Cell.Value
Case xlNotEqual: Test = Evaluate(.Formula1) <> Cell.Value
Case xlGreater: Test = Cell.Value > Evaluate(.Formula1)
Case xlLess: Test = Cell.Value < Evaluate(.Formula1)
Case xlGreaterEqual: Test = Cell.Value >= Evaluate(.Formula1)
Case xlLessEqual: Test = Cell.Value <= Evaluate(.Formula1)
End Select
ElseIf .Type = xlExpression Then
Application.ScreenUpdating = False
Cell.Select
Test = Evaluate(.Formula1)
Range(CurrentCell).Select
Application.ScreenUpdating = True
End If
If Test Then
If CellInterior Then
DisplayedColor = IIf(ReturnColorIndex, .Interior.ColorIndex, .Interior.Color)
Else
DisplayedColor = IIf(ReturnColorIndex, .Font.ColorIndex, .Font.Color)
End If
Exit Function
End If
End With
Next
If CellInterior Then
DisplayedColor = IIf(ReturnColorIndex, Cell.Interior.ColorIndex, Cell.Interior.Color)
Else
DisplayedColor = IIf(ReturnColorIndex, Cell.Font.ColorIndex, Cell.Font.Color)
End If
End Function
这篇关于使用VBA Excel功能获取背景颜色的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!