Conditional Format formula to Cells highlight.

[johncena]

dear friend my document column "J" has some values.it's like this..
ex-
=725
=725-50
=737
=2187+50-700
=753+45+25

in my column any cell values are reduced that cells i need to highlight.as per my example row number 2 & 4 should highlight.pls any body can make conditional format formula for this.thanks.

[6StringJazzer]

To the best of my knowledge, you cannot do this with conditional formulas. There is not a function that can find characters within formulas; for example, FIND will look at values but not the formula.

This can be done with VBA, see attached. This solution highlights any cell containing a formula with a "-". It does not otherwise change formatting of any other cells.

[johncena]

thanks a lot 6StringJazzer.your macro work very well.REP+.can u post your macro code here.it should work only with column "J"....

[6StringJazzer]

I have reposted my solution, which now contains two different solutions. Here is the code for the first solution that I posted earlier. This is the code associated with the button click:

Private Sub buttonHighlight_Click()
   Dim c As Range
   
   For Each c In Me.UsedRange
   
      If InStr(c.Formula, "-") > 0 Then
         c.Interior.Color = 65535
      End If
   
   Next c
   
End Sub

The second solution adds a user-defined function that returns the formula in a cell. This is a more general solution that is easier for a user to manage. In this case, I added a function called CELLFORMULA that returns the formula in a cell:

Public Function CELLFORMULA(c As Range) As String
   If c.Count > 1 Then
      CELLFORMULA = ""
   Else
      CELLFORMULA = c.Formula
   End If
End Function

Using this UDF, you can now construct a formula in conditional formatting to highlight the cell if it contains "-". For example, in cell B2 you would have the following rule to highlight the cell:

=NOT(ISERROR(FIND("-",CELLFORMULA(B2))))

Note: If the cell contains a "-" in any context, such as containing a negative number or a text string with a "-", this condition will be TRUE.

[johncena]

thanks u very much friend.