Vfrdtyky

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.

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.

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