Feedback by UserVoice

Michael Hutchinson

My feedback

  1. 934 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Thanks for supporting this feature. We won’t be able to 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 since named ranges can be dynamic, meaning that the range is calculated when the workbook is calculated.

    We’ll continue tracking votes for this suggestion.

    An error occurred while saving the comment
    Michael Hutchinson commented  · 

    @Jody Muelaner. Place this in a worksheet object.

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Me.Range("MyRange"),Target) Is Nothing Then
    Dim cell_x as range
    For each cell_x in Target

    If cell_x.Value="SpecialValue" Then

    With Cell_x
    .Interior.Color=vbRed
    .Font.Color=vbWhite
    End With

    Else

    With Cell_x
    .Interior.Color=xlNone
    .Font.Color=vbBlack
    End With

    End if

    next cell_x

    End If

    End Sub

    If you wanted the code to trigger on any change to the worksheet, you could remove the outer If statement and change the For each to iterate over Me.Range("MyRange"). There are other nuances to Conditional Formatting which you may want to duplicate, but hopefully this gives you a starter for ten.

    An error occurred while saving the comment
    Michael Hutchinson commented  · 

    You can get around this using Worksheet_Change() and intersect(x, target) style code in VBA...though admittedly not as user friendly.

    Michael Hutchinson supported this idea  · 
  2. 1,485 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Thanks very much for your votes. And to those who took the time to fill in the survey, thank you! This is a brief update to let y’all know that we’ve started work on this feature request along with the one on changing numbers to scientific notation.

    - Urmi [Msft]

    An error occurred while saving the comment
    Michael Hutchinson commented  · 

    I disagree with this request. As a default, numerical values should be treated as numerical. If the user wants to format the number as a textual value, i.e. with leading zeros, they should have to explicitly do so (i.e. precede with an apostraphe or format the cell as text). Which is what you currently have to do.

Feedback and Knowledge Base