插入箭头取决于If语句在VBA中
问题描述:
我试图根据一个单元格号是否大于另一个来插入箭头。我拥有的数据如下。插入箭头取决于If语句在VBA中
C E
6 20800 20400
7 5038 6003
8 46325 46325
if语句我现在是如下,我要添加到这一点。
For lngI = 1 To 3
If Worksheets("Front End").Cells(lngI + 5, 3).Value > Worksheets("Front End").Cells(lngI + 5, 5).Value Then
Worksheets("Front End").Cells(lngI + 5, 3).Interior.ColorIndex = 40
ElseIf Worksheets("Front End").Cells(lngI + 5, 3).Value < Worksheets("Front End").Cells(lngI + 5, 5).Value Then
Worksheets("Front End").Cells(lngI + 5, 3).Interior.ColorIndex = 35
ElseIf Worksheets("Front End").Cells(lngI + 5, 3) = Worksheets("Front End").Cells(lngI + 5, 5) Then
Worksheets("Front End").Cells(lngI + 5, 3).Interior.ColorIndex = 2
End If
Next lngI
所以目前所有我是基于列C细胞值是否更大,更小或等于在列E. 对应单元值高亮做因此,我想插入绿色箭头指向上方下一到小区(C,6)中的号码,小区(C,7)中号码旁边的红色向下箭头和小区(C,8)中号码旁边的黄色侧向箭头。
在此先感谢您的帮助。
答
如果您确定箭头出现在相邻单元而不是单元本身中,您可以在没有任何VBA的情况下执行此操作。
公式=IF(C1>D1,"é",IF(C1<D1,"ê","è"))
会给你正确的箭头,字体设置为Wingdings。
然后,您可以使用条件格式设置颜色。
编辑答案后接受:
这将箭头添加到单元格中的数字的结束。
更新后,单元格的内容将被视为文本 - 对单元格执行任何数学函数(例如SUM
)将返回#VALUE错误。
Sub Test()
Dim lngI As Long
Dim CharToAdd As String
Dim ColourToUse As Long
For lngI = 1 To 3
With Worksheets("Front End")
If .Cells(lngI, 3) > .Cells(lngI, 4) Then
CharToAdd = " é" 'Up arrow.
ColourToUse = -11489280 'Green.
ElseIf .Cells(lngI, 3) < .Cells(lngI, 4) Then
CharToAdd = " ê" 'Down arrow.
ColourToUse = -16776961 'Red.
Else
CharToAdd = " è" 'Right arrow.
ColourToUse = -16711681 'Yellow.
End If
'Add the character to the end of the number.
'Note - cell is now a text string.
.Cells(lngI, 3) = .Cells(lngI, 3) & CharToAdd
'Format last character in cell.
With .Cells(lngI, 3).Characters(Start:=Len(.Cells(lngI, 3)), Length:=1).Font
.Name = "Wingdings"
.Color = ColourToUse
End With
End With
Next lngI
End Sub
如果你想箭头出现在单元的开始:
Sub Test()
Dim lngI As Long
Dim CharToAdd As String
Dim ColourToUse As Long
For lngI = 1 To 3
With Worksheets("Front End")
If .Cells(lngI, 3) > .Cells(lngI, 4) Then
CharToAdd = "é " 'Up arrow.
ColourToUse = -11489280 'Green.
ElseIf .Cells(lngI, 3) < .Cells(lngI, 4) Then
CharToAdd = "ê " 'Down arrow.
ColourToUse = -16776961 'Red.
Else
CharToAdd = "è " 'Right arrow.
ColourToUse = -16711681 'Yellow.
End If
'Add the character to the start of the number.
'Note - cell is now a text string.
.Cells(lngI, 3) = CharToAdd & .Cells(lngI, 3)
'Format first character in cell.
With .Cells(lngI, 3).Characters(Start:=1, Length:=1).Font
.Name = "Wingdings"
.Color = ColourToUse
End With
End With
Next lngI
End Sub
我会考虑的条件格式规则.....
答
同样的方法,你可以尝试以下字符:
Sub Up_arrow()
ActiveCell.FormulaR1C1 = "á"
With ActiveCell.Font
.Name = "Wingdings"
.ColorIndex = 10
.TintAndShade = 0
End With
End Sub
Sub Down_arrow()
ActiveCell.FormulaR1C1 = "â"
With ActiveCell.Font
.Name = "Wingdings"
.ColorIndex = 46
.TintAndShade = 0
End With
End Sub
Sub Right_arrow()
ActiveCell.FormulaR1C1 = "à"
With ActiveCell.Font
.Name = "Wingdings"
.ColorIndex = 40 ' or 22
.TintAndShade = 0
End With
End Sub
什么是向上箭头?你的意思是像' - >'和''或'
“*旁边的*”是否在辅助列中? – FunThomas