Active Cell RRS feed

  • General discussion

  • Dear All

    If my ActiveCell value is Negative, I want the Active Cell RED how I can do that.

    Friday, January 22, 2016 3:41 AM

All replies

  • Only way I know of doing this is with VBA code as below. My reason for going with Conditional Formatting is so that when the selection is changed, the color will return to whatever color it is without the Conditional formatting and there is no need to identify the color to which the cell returns when another selection is made.

    Explanation of the code:

    • The code firstly removes all conditional format from the specified area
    • Then sets the conditional format for the selected range whether that be one cell or multiple cells and the condition is for an value < zero.

    To install the code:

    • Right click the worksheet tab name
    • Select View Code
    • Copy the code below and paste into the VBA editor.
    • See my comments in green where you will need to edit the code.
    • First edit whether to apply to a specific range or to entire worksheet.
    • Second edit whether you want to allow selection of multiple cells or single cells.
    • Third one whether you want the interior (background) to change color or the font to change color.
    • Close the VBA editor (cross with red background or turns red when cursor hover over it)
    • Save workbook as Macro enabled.
    • Ensure macros enabled in Options (Option to "Disable all macros with notification" should be OK)

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim rngAppliesTo As Range
        'Following line Optional to apply to specific range
        Set rngAppliesTo = Range("A2:G28")  'Edit "A2:G28" to the required range
        'Following line Optional (in lieu of previous line) to apply to entire worksheet
        'Set rngAppliesTo = Cells
        'Following line Optional. Comment out to allow selection of multiple cells
        If Target.Cells.Count > 1 Then Exit Sub
        If Not Intersect(Target, rngAppliesTo) Is Nothing Then
            With Target
                .FormatConditions.Add Type:=xlExpression, _
                        Formula1:="=" & Target.Address(0, 0) & "<0"
                With .FormatConditions(1)
                    'Uncomment/Comment following 2 lines for requird option
                    '(Don't Uncomment or comment both lines)
                    '.Interior.Color = RGB(255, 0, 0)    'red background
                    .Font.Color = RGB(255, 0, 0)         'red font
                    .StopIfTrue = False
                End With
            End With
        End If
    End Sub

    Regards, OssieMac

    Sunday, January 24, 2016 12:13 PM