Why is my conditional format offset when added by VBA?

Question

I was trying to add conditional formats like this:

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

Tried this, worked fine, as expected, then tried to adapt this as VBA-Code with following code, which is working, 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, .row is providing the right row while in debug, however my added conditional-formula seems to be one or more rows off - depending on my solution for setting the row. So I am ending up with a conditional formatting, which has an offset to the row, which should have been formatted.

In the dialog it is then =($G6<>"") or G3 or G100310 or something like this. But not my desired G5.

Setting the row has to be dynamicall, because this is used to setup conditional formats on different worksheets, which can have their data starting at different rows.

I was suspecting my With arrangement, but it did not fix this problem.

edit: To be more specific, this is NOT a UsedRange problem, having the same trouble with this:

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

My Data looks like this:

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

When I execute this edited code on Excel 2007 and lookup the formula in the conditional dialog it is =($G1048571<>"") - it should be =($G1<>""), then everything works fine.

Whats even more strange - this is an edited version of a fine working code, which used to add conditional formats for each row. But then I realized, that it's possible to write an expression, which formats a whole row or parts of it - thought this would be adapted in a minute, and now this ^^

edit: Additional task informations

I use conditional formatting here, because this functions shall setup a table to react on user input. So, if properly setup and a user edits some cell in my conditionalized column of this tabel, the corresponding row will turn green for the used range of rows.

Now, because there might be rows before the main header-row and there might be a various number of data-columns, and also the targeted column may change, I do of course use some specific informations.

To keep them minimal, I do use NamedRanges to determine the correct offset and to determine the correct DefaultProgessColumn.

GetTitleRow is used to determine the header-row by NamedRange or header-contents.

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

Corrected my Formula1, because I found the construct before not well formed.

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

strMatchCol1 - is the name of a range.

It seems, that i have to use .row+2 to get this right - but WHY?! and why does it only fix this on this one specific worksheet?! - I must be missing something here - any ideas?! Many thanks to all supporters! — Jook, Sep 14 '12 at 08:57
Be careful when using `.UsedRange` - see http://stackoverflow.com/questions/7423022/excel-getting-the-actual-usedrange and probably some other threads of SO — JMax, Sep 14 '12 at 09:00
I don't think you are getting what you expect from UsedRange. It doesn't return the last row like you might expect. What are you trying to target? — Omnikrys, Sep 14 '12 at 09:02
the point is, I am not trying to get the last row. I only care about the start, and when I check under debug, UsedRange delivers what I would expect. I want to use it, so that I don't have to specify, how wide or long my datacols are, so I have it easy adaptable. — Jook, Sep 14 '12 at 09:09
@JMax, thanks for the reminder, checked this, not the problem. — Jook, Sep 14 '12 at 09:16
in a way, I solved this - while adapting my code, let's see if anybody else will get to the mess behind this :) — Jook, Sep 14 '12 at 09:49
It is something weird with how excel is handling the cell reference. You know how it automagically changes the reference from cell to cell when you paste a formula? Well it is doing that but the reference is way off. This happens after the first run. Open workbook, setup a test page, run code... it works. Do it again it fails by a little bit. Do it again and it fails by more. Going to pick at it and see if I can sort it. — Omnikrys, Sep 14 '12 at 09:50

score 4 · Accepted Answer · answered Sep 14 '12 at 10:00

4

Got it, lol. Set the ActiveCell before doing the grunt work...

ActiveSheet.Range("A1").Activate

Excel is pulling its automagic range adjusting which is throwing off the formula when the FromatCondition is added.

answered Sep 14 '12 at 10:00

Omnikrys

2,538
18
13

congrats! that was it ... you earned it. However - i am not that happy about it, because this still feels like a workaround and not a solution. However, I think it's a problem of Excel until someone proofs it's not. – Jook Sep 14 '12 at 10:03
Yeah that is why my initial comment was pointing at some kind of bug. At the very least I want to call it "unexpected consequence" as in "why the heck is it doing this?". – Omnikrys Sep 14 '12 at 10:05
1

If you want to go deeper into VBA as programming, you will notice that using "ActiveSheet", "ActiveCell", "Select" and "Offset" are bad custom for this reason and others. VBA code seldomly remains the same and people will add and change stuff all the time. Not naming your objects explicitly (eg. set oBook = workbooks.open("smtg")) but referring to them as Active may bring you pain. Instead of select and offset it's better to define a range (eg. set oRange = <'Range def>) and loop through this range. Or load it into an array first and loop through it after. – html_programmer Sep 14 '12 at 10:42
@KimGysen your right in some parts, and I am using the mentioned techniques on other ocasions - here however it is seems not optimal. When you think otherwise, I would be happy to see another solution to this. I am too with you on _select_ (that is why i don't like the current solution) but _ActiveSheet_,_ActiveCell_ and _Offset_ have their _good_ powers and are useful in many cases - like this one i.e.. Not that _select_ has no reason to exist, it's just often abused to do unfit tasks - like excel is often abused as a database. – Jook Sep 14 '12 at 11:23
The reply to your question will work for now so I will not break it down if it's helpful. However (and this is just a note), if there is a regular end user (no VBA knowledge) who inserts a few lines on top to add let's say a date or some other random information (I experienced some unaware end users just don't know they shouldn't), the code won't work anymore. To catch this problem, it would for example be a good practise to make the range defined a named range; via VBA you can extend this range dynamically when needed. If then someone inserts a line, the --> – html_programmer Sep 14 '12 at 11:59
range will remain intact (whether it's a row or column insert). In my experience, it is advised to always make the code as dynamic as possible; in case of Excel, this means to look at the architecture from a birds eye view and see in your mind how ranges can possibly shift with the purpose of removing the main possible bugs. Personally, I would through the format condition with a counter. – html_programmer Sep 14 '12 at 12:04
1

In brief, what I mean is that code execution should never be depending on which sheet or cell is active in general. I will write some code of what I have in mind. Of course the decision is entirely up to you. – html_programmer Sep 14 '12 at 12:06

score 4 · Answer 2 · answered Sep 14 '12 at 13:35

The reason that Conditional Formatting and Data Validation exhibit this strange behavior is because the formulas they use are outside the normal calculation chain. They have to be so that you can refer to the active cell in the formula. If you're in G1, you can't type =G1="" because you'll create a circular reference. But in CF or DV, you can type that formula. Those formulas are disassociated with the current cell unlike real formulas.

When you enter a CF formula, it's always relative to the active cell. If, in CF, you make a formula

=ISBLANK($G2)

and you're in A5, Excel converts it to

=ISBLANK(R[-3]C7)

and when that gets put into the CF, it ends up being relative to the cell it's applied to. So in row 2, the formula comes out to

=ISBLANK($G655536)

(for Excel 2003). It offsets -3 rows and that wraps to the bottom of the spreadsheet.

You can use Application.ConvertFormula to make the formula relative to some other cell. If I'm in row 5 and the start of my range is in row 2, I make the formula relative to row 8. That way the R[-3] will put the formula in A5 as $G5 (three rows up from 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

I only offset .Cells(1) row-wise because the column is absolute in this example. If both row and column are relative in your CF formula, you need more offsetting. Also, this only works if the active cell is below the first cell in your range. To make it more general purpose, you would have to determine where the activecell is relative to the range and offset appropriately. If the offset put you above row 1, you would need to code it so that it referred to a cell nearer the bottom of the total number of rows for your version of Excel.

If you thought selecting was a bit of a kludge, I'm sure you'll agree that this is worse. Even though I abhor unnecessary Selecting and Activating, Conditional Formatting and Data Validation are two places where it's a necessary evil.

html_programmer · Answer 3 · 2012-09-14T13:02:14.817

A brief example:

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

Regarding my comments given on the other reply:

If you have to do this for many rows, it is definitely faster to load the the entire range into memory (an array) and check the conditions within the array, after which you do the writing on those cells that need to be written (formatted).
I could agree that this technique is not "necessary" in this case - however it is good practise because it is flexible for many (any type of) customizations afterwards and easier to debug (using the immediate / locals / watches window).
I'm not a fan of Offset although I don't state it doesn't work as it should and in some limited scenarios I could say that the chance for problems "could" be small: I experienced that some business users tend to use it constantly (here offset +3, there offset -3, then again -2, etc...); although it is easy to write, I can tell you it is hell to revise. It is also very often subject to bugs when changes are made by end users.
I am very much "for" the use of headers (although I'm also a fan of reducing database capabilities for Excel, because for many it results in avoiding Access), because it will allow you very much flexibility. Even when I used columns 1 and 2; better is it to retrieve the column nr dynamically based on the location of the named range of the header. If then another column is inserted, no bugs will appear.

Last but not least, it may sound exaggerated, but the last time, I used a class module with properties and functions to perform all retrievals of potential data within each sheet dynamically, perform checks on all bugs I could think of and some additional functions to execute specific tasks.
So if you need many types of data from a specific sheet, you can instantiate that class and have all the data at your disposal, accessible through defined functions. I haven't noticed anyone doing it so far, but it gives you few trouble despite a little bit more work (you can use the same principles again over and over).
Now I don't think that this is what you need; but there may come a day that you need to make large tools for end users who don't know how it works but will complain a lot about things because of something they might have done themselves (even when it's not your "fault"); it's good to keep this in mind.

Be careful using Integer to count rows. Consider using Long instead as the number of rows can exceed what an Integer can hold. — Dick Kusleika, Sep 14 '12 at 16:42
Agreed, in Access I use Longs too. In Excel too if I know there will be many "records". — html_programmer, Sep 14 '12 at 17:20
@DickKusleika - thanks for your great explanaition in your answear! KimGysen, thank you for this technique demo, but this solution is not fit for my problem. As stated, I use NamedRanges and ArrayCopies too, but the task here requires a different approach, althoug I use a NamedRange now in my ConditionalFormula. I will add an explanaition for this in my question. However, your loop construction is an interesting alternative to my LBOUND/UBOUND method, thank you for that! — Jook, Sep 17 '12 at 09:06

Why is my conditional format offset when added by VBA?

3 Answers3

Linked