+ Reply to Thread
Results 1 to 5 of 5

Conditional Format formula to Cells highlight.

  1. #1
    Forum Contributor
    Join Date
    02-14-2010
    Location
    oman
    MS-Off Ver
    Excel 2003
    Posts
    384

    Post Conditional Format formula to Cells highlight.

    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.
    Last edited by johncena; 03-23-2010 at 04:09 AM.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,256

    Re: Conditional Format formula to Cells highlight.

    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.
    Attached Files Attached Files
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Contributor
    Join Date
    02-14-2010
    Location
    oman
    MS-Off Ver
    Excel 2003
    Posts
    384

    Re: Conditional Format formula to Cells highlight.

    thanks a lot 6StringJazzer.your macro work very well.REP+.can u post your macro code here.it should work only with column "J"....
    Last edited by johncena; 03-22-2010 at 09:31 AM.

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,256

    Re: Conditional Format formula to Cells highlight.

    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:

    Please Login or Register  to view this content.
    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:

    Please Login or Register  to view this content.
    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.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    02-14-2010
    Location
    oman
    MS-Off Ver
    Excel 2003
    Posts
    384

    Re: Conditional Format formula to Cells highlight.

    thanks u very much friend.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1