Feedback by UserVoice

anomalous

My feedback

  1. 1 vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    We’ll send you updates on this idea

    anomalous supported this idea  · 
    anomalous shared this idea  · 
  2. 1 vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    We’ll send you updates on this idea

    anomalous shared this idea  · 
  3. 1,027 votes

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    We’ll send you updates on this idea

    Thanks for supporting this feature. We won’t be able to offer this in the near future, but we understand that it would be a nice improvement. Even though it seems like a straightforward idea, it is quite complex. Old versions of Excel will not support the new behavior, so there would need to be some hybrid approach that will be suitable for old and new versions. Also, named ranges can be dynamic, meaning that the range might change and conditional formatting rules would have to be adjusted when the workbook is calculated. There are lots of cases to consider and deal with properly.

    We’ll continue tracking votes for this suggestion.

    An error occurred while saving the comment
    anomalous commented  · 

    Here is a bunch of workaround scenarios for this - hope it's useful to someone

    anomalous supported this idea  · 
    An error occurred while saving the comment
    anomalous commented  · 

    This (very simple) VBA User Defined Formula can be used to avoid all that row() and column() checking...

    Function NW_IsWithinRange(CellReference As Range, RangeReference As Range) As Boolean
    If CellReference.Row >= RangeReference.Row And _
    CellReference.Row <= RangeReference.Row + RangeReference.Rows.Count - 1 And _
    CellReference.Column >= RangeReference.Column And _
    CellReference.Column <= RangeReference.Column + RangeReference.Columns.Count - 1 Then
    NW_IsWithinRange = True
    Else: NW_IsWithinRange = False
    End If
    End Function

    An error occurred while saving the comment
    anomalous commented  · 

    I have a solution to this - you won't like it but it works, it doesnt need VBA. I've used it for years without any problems, it doesnt noticably increase the file size or impact calculation times (insofar as I have noticed). My preferrewd solution would be for MS to enable =indirect() or Named Ranges in the range selection. But my approach does work reliably.

    Apologies if someone already posted some variant of this solution, I havent read through all the comments (there's just too many of them).

    So the solution is to shift the definition of the range from the *range definition* to the formula logic.

    Firstly using a blank spreadsheet, create a conditional format set the range to cover $A:$XFD - ie every cell on the worksheet - then using a formula to determine which cells to format, maybe use this horror - you'll need a blunt instrument to crack this not ;-)

    =AND(ROW()>7,ROW()<10,COLUMN()>4,COLUMN()<10,NOT(ISBLANK(A1)))

    and set the format to be something obvious - perhaps a red background
    enter a bunch of random text copy stuff anywhere and I guarantee you wont have any duplicated Conditional Formatting.

    The other way is to (also apply your conditional format to every cell in the entire worksheet, but use a separate sheet as a *map* of where the Conditional formatting should apply. I used a few other variations on these methods - but they all amount to the same thing - apply your conditional format to ever cell in the sheet and have overly complex formulae - I attached a simple example worksheet with 2 examples show - you'll work out the rest...

    Cheers - NigelW

  4. 1 vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    We’ll send you updates on this idea

    anomalous shared this idea  · 
  5. 1 vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    We’ll send you updates on this idea

    anomalous shared this idea  · 

Feedback and Knowledge Base