Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    If/Then Statements (Formulas) And Cell Colors

    Does anyone know if it is possible to enter an if/then statement/formula in a cell that will result in another cell changing colors/blinking if the condition is/is not met? For example, if the cell value in A1="Yes" or a certain numerical value (say, >0), the the color of B1 is green and blinking.

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: If/Then Statements (Formulas) And Cell Colors

    Try Conditional Formatting.

    Set the formatting for a formula: =A1="YES"

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: If/Then Statements (Formulas) And Cell Colors

    KW is quite right to suggest conditional formatting to highlight a cell, as Excel does not directly support flashing colors. You cannot do it by using worksheet functions. However if you must have a flashing cell, it can be done by using event procedures such as OnTime, Worksheet Activate, DeActivate and Change. It does involve continuously changing the color in the destination cell. Firstly you need something like the following to start the process <pre>Sub SetFlash()
    Application.OnTime Now + TimeValue("00:00:001"), "FlashCell"
    End Sub</pre>

    where FlashCell is the name of a routine to be called at the given (very small) interval. It might look like <pre>Sub FlashCell()
    If Range("B1").Font.ColorIndex = 43 Then
    Range("B1").Font.ColorIndex = 6
    Else
    Range("B1").Font.ColorIndex = 43
    End If
    If Range("A1").Value = "Yes" Then
    SetFlash
    Else
    StopFlash
    End If
    End Sub</pre>

    These and one or two others like StopFlash, can exist in a general module. StopFlash is not essential, but I put something in that could be called when the sheet is de-activated (no point in flashing something nobody can see). Thats where you need to have worksheet event procedures. These are not int the general module but in the actual worksheet object. I am attaching a file to demonstrate the method. Type Yes into A1 and see the effects. You will notice B3 changes as well, and that is based on conditional formatting, which is the way I would go, especially if you will be working on the sheet that contains the flashing cell. Any actions have to be interrupted by the procedures and that may be cumbersome. Maybe somebody else has a more elegant solution.

    Andrew C
    Attached Files Attached Files

Posting Permissions

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