Excel Conditional formatting formula and ranges (VBA)

0
votes

I have a FormatCondition. I am reading the .Formula1 and the .AppliesTo

I want to find if the Formula contains a cell reference that will change across the range specified by the AppliesTo, eg =NOT(ISNUMBER(C3)) in a range C3:D10.

From what I can tell, that reference might be relative (eg C3) or mixed (eg $C3 or C$3) but not absolute. It will also be the 'top-left most cell in the applied range' based on https://www.ablebits.com/office-addins-blog/2014/08/07/relative-absolute-cell-references-excel-conditional-formatting/ and some other sites.

If the range is something like C3:D10 then that's easy. Find the top left using eg myRange.Cells(1, 1), convert it to a string using .Address or similar and then look for that string in the Formula, along with relevant variations adding a $. Sure, I'd have to be careful about things like strings (eg top left cell is C3' and formula is="CC3NE"`), but it's possible (still, I'd welcome suggestions on how to do that better, but that's not the question).

The real problem is if the range is more complex, as seems to happen often with Conditional Formatting. An example is $CQ$39:$FT$39,$BE$39,$BE$8:$BE$9,$CU$8:$FT$9,$CU$12:$FT$14,$BE$12:$BE$14,$BE$16:$BE$30,$CQ$16:$FT$30,$CQ$32:$FT$36,$BE$32:$BE$36. .Cells(1, 1) doesn't give the right answer for that (BE8, according to the formula that Excel made).

Further, what if the range were something like $A$5,$E$1 - there is no 'top left'. So how does excel (or the user) decide which should be in the formula?

Thus the primary question is: What is the system for Excel determining which cell to use in a formula, because I don't believe it is 'top left' - it might be 'find the topmost row, then find the leftmost cell there', or it might be the reverse, or something completely different.

The second question is: how best can I find that cell from a given range?

The bonus question (I'm happy if this doesn't get answered here) is: Is there a good way to then find references to that cell in a formula, including relative and mixed versions of the cell?

Note that these strange ranges are coming about from Rows and Columns being Cut and Pasted on a sheet with Conditional Formatting, with Excel chopping up the conditional formatting as a result (changing the range, and changing the formula, both without user input). So the priority is to deal with whatever Excel would set the formula to be on its own in such a scenario, not necessarily what a user might do - but handling both would be even better.

2
excelvbaconditional-formatting
If you copy / pasted as paste.special values or paste.special formulae, this would leave any conditional formatting in place and untouched... – Solar Mike
Thanks for the heads up, I'm aware of that. But with respect, this question is about dealing with the formula and applies to, not about the issue of the conditional formatting being messed up (which is its own separate question, and a big one). – James Carlyle-Clarke
It seems that your question contains many questions, and they are all mixed in each other. I was reading it and my concentration was getting lost as your statements keeps jumping between questions. You should separate them to get good answers. Same goes to your own way of problem solving techniques; if you see all the problems as one big problem it would be very difficult to solve, but if you separate them then each one would be a small one can be solved easily. – usmanhaq
@usmanhaq, Thanks for the input. I've edited to try to make it clearer. Truth be told, I normally aim to be more concise - but last time I asked a question, I got told that by giving only a very limited aim or need I was making a mistake, as they needed a bigger picture to see if the thing I was asking about was even the way to do it. I don't totally agree with that point, but still I tried to provide a bit of context, especially as this is not an area of expertise for me. – James Carlyle-Clarke

2 Answers

1
votes

Thanks for updating your question and find below my comments

What is the system for Excel determining which cell to use in a formula, because I don't believe it is 'top left' - it might be 'find the topmost row, then find the leftmost cell there', or it might be the reverse, or something completely different.

Usually range in Excel is similar to a box or rectangle with a fixed length and width, e.g. B3:C8. In case of this simple box type ranges the top left most cell is the base for all formulas in conditional formatting. In other words at first formula evaluation this cell's value is checked against the formula applied in conditional formating. (Let’s call this cell as mother cell)

For example

  • If conditional formatting is applied to cell A1:D10 and a conditional formula is B2 > 0, it means apply formatting to cell A1 if value of B2 > 0, and for others apply formatting to any cell if value of a cell with an offset of (1, 1) (that was B2 in A1 case) has value greater then 0. i.e for formatting of B2 value of C3 will be checked.
  • If conditional formatting is applied to cell A1:D10 and a conditional formula is AND($B1 >= 30, $B1 <= 60) it means apply formatting to cell A1 if value of condition is true, but now the difference comes, because B is now static because of $B, for conditional formatting of B2 the condition will again be AND($B1 >= 30, $B1 <= 60) same for C1 and D1, but for A2, B2, C2 and D2 it will be AND($B2 >= 30, $B2 <= 60)

In first case the first cell that was checked against the formula was A1 and even in second case the cell that was checked against the conditional formula was A1 (which we called mother cell)

Now if the range is not like a box, it is a mixed range separated by commas, even in this case if we make a box around that range the mother cell is the cell in upper left corner, it is not the first cell of the range but first cell of the box.

For your 2nd question how best can I find that cell from a given range?

If you want to find the mother cell in a box like simple Range, it is just cells (1, 1)

If you want to find that cell in mixed range may be there are some functions out there but I don’t know of any; I would find that by using loop like below

Sub find_topleft()

 Dim r, full_range As Range

 Set r = Range("$O$8:$O$17,$Q$4:$Q$13,$S$4:$S$6")

 Set full_range = Sheets("Sheet1").UsedRange

 row_num = full_range.Rows.Count + full_range.Row - 1
 col_num = full_range.Columns.Count + full_range.Column - 1

 For Each c In r
    If c.Row < row_num Then
    row_num = c.Row
    End If

    If c.Column < col_num Then
    col_num = c.Column
    End If
 Next

 Debug.Print row_num
 Debug.Print col_num

End Sub

Is there a good way to then find references to that cell in a formula, including relative and mixed versions of the cell?

That cell is directly linked with the formula as stated in answer to your first question.

1
votes

First answer: Short version: Excel does indeed use the most top left cell - but it does that EVEN IF THE CELL IS NOT PART OF THE RANGE.

I created a blank sheet, selected A5,E1, and put in a conditional format of NOT BLANK (which Excel does with a formula). Excel created the following:

Range: $E$1,$A$5

Formula: =LEN(TRIM(A1))>0

So even though A1 is not part of the range, that's what got used in the formula.

I checked this by duplicating it around the sheet, and it stayed consistent.

Thus Excel finds the leftmost column in the range, and the topmost row in the range, and combines them to produce the cell that should be considered the 'top left' of the range, even if the resulting cell is not actually part of the range.

This suggests an answer to the second question too: Find the top most row in the range; find the left most column; then use a cell that combines the two to compare with the formula. You could do that by going through the range string, or maybe cell by cell through the range itself; perhaps there is a better way to do it (suggestions welcome).

It's not pretty, but it would work, if I can find a good way to do those two things.

I would still welcome:

  • anyone who can improve on this answer about which cell is used in the formula (I did not test this extensively)
  • anyone who can suggest a good way to find the 'top left cell' as above
  • anyone who can suggest a good way to search the formula string for that top left cell

EDIT - Here's an alternate code method to Usmanhaq's excellent method - this goes through the range as a string: 

Private Function FindTopLeft(rangeStr As String, rowabs As Boolean, colabs As Boolean) As String
    newRange = Replace(rangeStr, ":", ",")
    newRangeArray = Split(newRange, ",")
    Dim lowestRow As Long
    Dim lowestCol As Long
    lowestRow = 2147483647
    lowestCol = 2147483647

    For Each cell In newRangeArray
        cell = Trim(cell)
        If cell <> "" Then
            cCol = range(relativeCell).Column
            cRow = range(relativeCell).Row
            If cCol < lowestCol Then lowestCol = cCol
            If cRow < lowestRow Then lowestRow = cRow
        End If
    Next

    FindTopLeft = Cells(lowestRow, lowestCol).Address(rowabs, colabs)

End Function