VBA条件格式如果单元格不在之间 [英] VBA Conditional Formatting if Cell Not Between

问题描述

我需要使用VBScript将条件格式化应用到单元格，以更改单元格的背景颜色，如果其值不在两个其他单元格的值之间：

来自宏的代码

  Selection.FormatConditions.Add类型：= xlCellValue，运算符：= xlNotBetween，_ 
 Formula1：== $我$ 28，Formula2：== $ J $ 28
 Selection.FormatConditions（Selection.FormatConditions.Count）.SetFirstPriority 
 With Selection.FormatConditions（1）.Interior 
 .PatternColorIndex = xlAutomatic 
 .Color = 255 
 .TintAndShade = 0 
结束
 Selection.FormatConditions（1）.StopIfTrue = False 
 End Sub 
  

我转换为vbscript不工作

  priceRange =K& rowNum + 2 
 objWorkSheet.Range（priceRange）.FormatConditions.Add类型：= 1，运算符：= 2，Formula1：= = $ I $& finalRowNum + 1&，Formula2：== $ J $& finalRowNum + 1&
 objWorkSheet.Range（priceRange）.FormatConditions（objExcel.Selection.FormatConditions .Count）.SetFirstPriority 
 objWorkSheet.Range（priceRange）.FormatConditions（1）.Interior.PatternColorIndex = -4105 
 objWorkSheet.Range（priceRange）.FormatConditions（1）.Interior.Color = 255 
 objWorkSheet.Range（priceRange）.FormatConditions（1）.Interior.TintAndShade = 0 
 objWorkSheet.Range（priceRange）.FormatConditions（1）.StopIfTrue = False 
  / pre> 
 
解决方案
有效的代码
 code>设置rng = objWorkSheet.Range（K& rowNum + 2）
 
'vbscript不支持命名参数，只有位置
设置fc = rng.FormatConditions.Add（1，2，_ 
= $ I $ & finalRowNum，_ 
= $ J $& finalRowNum）
 fc.SetFirstPriority 
 
带有fc.Interior 
 .PatternColorIndex = -4105 
 .Color = 255 
 .TintAndShade = 0 
结束
 
 fc.StopIfTrue = False 
  
 
I need to apply conditional formatting to a cell using VBScript to change the background color of the cell if it's value is not between the value of two other cells:



code from the macro
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, _
    Formula1:="=$I$28", Formula2:="=$J$28"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 255
    .TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
My conversion to vbscript that isn't working
    priceRange = "K"&rowNum + 2
    objWorkSheet.Range(priceRange).FormatConditions.Add Type:=1, Operator:=2, Formula1:="=$I$"&finalRowNum + 1&"", Formula2:="=$J$"&finalRowNum + 1&""
    objWorkSheet.Range(priceRange).FormatConditions(objExcel.Selection.FormatConditions.Count).SetFirstPriority
    objWorkSheet.Range(priceRange).FormatConditions(1).Interior.PatternColorIndex = -4105
    objWorkSheet.Range(priceRange).FormatConditions(1).Interior.Color = 255
    objWorkSheet.Range(priceRange).FormatConditions(1).Interior.TintAndShade = 0
    objWorkSheet.Range(priceRange).FormatConditions(1).StopIfTrue = False

解决方案
The code that worked
Set rng = objWorkSheet.Range("K" & rowNum + 2)

'vbscript doesn't support named arguments, only positional
Set fc = rng.FormatConditions.Add(1, 2, _
                                 "=$I$" & finalRowNum, _
                                 "=$J$" & finalRowNum)
fc.SetFirstPriority

With fc.Interior
    .PatternColorIndex = -4105
    .Color = 255
    .TintAndShade = 0
End With

fc.StopIfTrue = False


                        
这篇关于VBA条件格式如果单元格不在之间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！