Geek asks and answers

Why does my conditional format shift when adding VBA?

I tried to add conditional formats as follows:

If the expression =($G5<>""), then make the set green, use this for $ A $ 5: $ H $ 25.

I tried this, it worked fine, as expected, then I tried to adapt it as a VBA code with the following code, which works, but not as expected:

With ActiveSheet.UsedRange.Offset(1)
  .FormatConditions.Delete
  'set used row range to green interior color, if "Erledigt Datum" is not empty
  With .FormatConditions.Add(Type:=xlExpression, _
                             Formula1:="=($" & cstrDefaultProgressColumn & _
                                                      .row & "<>"""")")
        .Interior.ColorIndex = 4
      End With
End With

The problem is .rowproviding the correct line during debugging, however my conditional formula added seems to be one or more lines depending on my decision to set the line. Therefore, I am ending conditional formatting, which has an offset to the string that needs to be formatted.

=($G6<>"") G3 G100310 - . G5.

, , , .

, With, .

edit: , UsedRange, ​​ :

Dim rngData As Range
Set rngData = ActiveSheet.Range("A:H") 'ActiveSheet.UsedRange.Offset(1)

rngData.FormatConditions.Delete

With rngData.FormatConditions.Add(Type:=xlExpression, _
                                  Formula1:="=($" & cstrDefaultProgressColumn & _
                                                  1 & "<>"""")")
    .Interior.ColorIndex = 4
End With

:

1 -> empty cells
2 -> empty cells
3 -> empty cells
4 -> TitleCols -> A;B;C;...;H
5 -> Data to TitleCols
. .
. .
. .
25

Excel 2007 =($G1048571<>"") - =($G1<>""), .

- , . , , , - , , ^^

:

, . , - , .

, , , , , , .

, NamedRanges DefaultProgessColumn.

GetTitleRow NamedRange .

With ActiveSheet.UsedRange.Offset(GetTitleRow(ActiveSheet.UsedRange) - _
                                ActiveSheet.UsedRange.Rows(1).row + 1)

​​ 1, , .

Formula1:="=(" & Cells(.row, _
           Range(strMatchCol1).Column).Address(RowAbsolute:=False) & _
           "<>"""")"

strMatchCol1 - .

+2
3

, lol. ActiveCell , grunt...

ActiveSheet.Range("A1").Activate

Excel , , FromatCondition.

+3

, , , . , . G1, =G1="", . CF DV . ​​ .

CF, . CF

=ISBLANK($G2)

A5, Excel

=ISBLANK(R[-3]C7)

CF, , . , 2

=ISBLANK($G655536)

( Excel 2003). -3 .

Application.ConvertFormula, . 5, 2, 8. , R [-3] A5 $G5 ( A8).

Sub test()

    Dim cstrDefaultProgressColumn As String
    Dim sFormula As String

    cstrDefaultProgressColumn = "$G"

    With ActiveSheet.UsedRange.Offset(1)
        .FormatConditions.Delete
        'set used row range to green interior color, if "Erledigt Datum" is not empty

        'Build formula
        sFormula = "=ISBLANK(" & cstrDefaultProgressColumn & .Row & ")"

        'convert to r1c1
        sFormula = Application.ConvertFormula(sFormula, xlA1, xlR1C1)

        'convert to a1 and make relative
        sFormula = Application.ConvertFormula(sFormula, xlR1C1, xlA1, , ActiveCell.Offset(ActiveCell.Row - .Cells(1).Row))

        With .FormatConditions.Add(Type:=xlExpression, _
                                 Formula1:=sFormula)

            .Interior.ColorIndex = 4
        End With

    End With

End Sub

.Cells(1) , . CF, . , , . , , . 1, , Excel.

, , , , . , , - , .

+4

:

Sub Format_Range()

Dim oRange          As Range
Dim iRange_Rows     As Integer
Dim iCnt            As Integer


'First, create a named range manually in Excel (eg. "FORMAT_RANGE")
'In your case that would be range "$A$5:$H$25". 
'You only need to do this once, 
'through VBA you can afterwards dynamically adapt size + location at any time. 

'If you don't feel comfortable with that, you can create headers 
'and look for the headers dynamically in the sheet to retrieve 
'their position dynamically too. 

'Setting this range makes it independent
'from which sheet in the workbook is active
'No unnecessary .Activate is needed and certainly no hard coded "A1" cell. 
'(which makes it more potentially subject to bugs later on) 
Set oRange = ThisWorkbook.Names("FORMAT_RANGE").RefersToRange
iRange_Rows = oRange.Rows.Count

For iCnt = 1 To iRange_Rows
    If oRange(iCnt, 1) <> oRange(iCnt, 2) Then
        oRange(iCnt, 2).Interior.ColorIndex = 4
    End If
Next iCnt

End Sub

, :

, () , , ().
, "" - , ( ) ( // ).
Offset, , , , , " " : , - ( +3, -3, -2 ..); , , , . .
"" ( Excel, ), . 1 2; nr, . , .

, : , , , , .
, , , . , - , , ( ).
Now I do not think that this is what you need; but there may come a day when you need to make great tools for end users who don’t know how it works, but will complain a lot about things because they could do it themselves (even if this is not your “mistake”) ; remember this well.

+1
source

More articles:


All Articles