+ Reply to Thread
Results 1 to 11 of 11

Can't get a specific text in a range of cells to blink

  1. #1
    Registered User
    Join Date
    10-06-2012
    Location
    Singapore
    MS-Off Ver
    Excel 365
    Posts
    50

    Can't get a specific text in a range of cells to blink

    I have a column (say Column B) of cells that will display the words "Expiring soon" based on
    the values in another column (say Column A) of cells.

    Cells in Column B contain formula to determine when the words "Expiring soon" should be displayed. Cells in Column A contain numbers which represent number of days.

    I tried to find from various Excel forums to get VBA coding that can make the words "Expiring soon" to blink. I tried to adapt a set of VBA coding which is closest to what I want, but it does seem to work.

    I need some experts' help. I've attached a sample Excel file to illustrate what I want to achieve. Any help from Excel experts is very much appreciated. Thank you.

    KS Chan
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    04-14-2018
    Location
    Vermont, USA
    MS-Off Ver
    Office 365
    Posts
    72

    Re: Can't get a specific text in a range of cells to blink

    I'm sure this is not the best solution, but here's how I got it to blink. There were several problems with your worksheet. First, you had the font color determined by Conditional Formatting, so you couldn't change it with code. Second, you had the Worksheet_Change event in Module 1 but it needed to be in the Sheet 1 coding.

    In Sheet 1 Code:
    Please Login or Register  to view this content.
    In Module 1:
    Please Login or Register  to view this content.
    It should blink red 10 times then go black. I added an escape: the DoEvents command allows you to click in cell A1 which will stop the macro.
    Attached Files Attached Files
    Last edited by Jeff Ho; 05-27-2018 at 11:54 AM.

  3. #3
    Registered User
    Join Date
    10-06-2012
    Location
    Singapore
    MS-Off Ver
    Excel 365
    Posts
    50

    Re: Can't get a specific text in a range of cells to blink

    Thank you, Jeff for your reply and coding.
    You mentioned that by using Conditional Formatting to determine the font color would interfer with the coding. So I got removed the conditioning formatting. Then I followed your instructions - putting your Worksheet_Change event in Sheet 1 and the Sub Blinker into Module 1.
    However, nothing happens. Is there something that I missed to trigger the coding to work? My original intention was to have the text to blink when the workbook is open, that was why I put the "StartBlink" in "ThisWorkbook" using "Private Sub Workbook_Open()". I tried to put your Sub Blinker in "ThisWorkbook" but it didn't work. Instead I received a Compile error: Argument not optional. Any suggestions as to what I should do? Have I got to make some changes in your coding to make it work? Thank you.

  4. #4
    Registered User
    Join Date
    10-06-2012
    Location
    Singapore
    MS-Off Ver
    Excel 365
    Posts
    50

    Re: Can't get a specific text in a range of cells to blink

    Sorry Jeff, I didn't notice the Blink text sample1.xlsm file you attached. I tried it and it shows the text blinking. How do I applied the blinking to all the cells in the range (B4 to B10) that have the word "Expiring soon"? I tried to change the Range("B4") to Range("B4:B10"), but I received a Run-time error "13", Type Mismatch. Can you suggest what I should do? Thank you.

  5. #5
    Registered User
    Join Date
    04-14-2018
    Location
    Vermont, USA
    MS-Off Ver
    Office 365
    Posts
    72

    Re: Can't get a specific text in a range of cells to blink

    I was working off your original setup, which was based on the Sheet_Change event. The Change event fires whenever you make any change on the sheet. If you type a number from 1 to 15 in A4, cell B4 will blink. In fact, if you type anything in any cell on the sheet, and the cell to its right contains "Expiring soon", it will blink. But it's one at a time.

    I'd have to figure out the changes to the Blinker procedure that would make them all blink at once. I don't have the time to do that tonight, though.

    Do you have a lot of records to apply this to? Is there a reason the conditional formatting isn't sufficient to make the users take note of what's coming due?

    - Jeff

  6. #6
    Registered User
    Join Date
    10-06-2012
    Location
    Singapore
    MS-Off Ver
    Excel 365
    Posts
    50

    Re: Can't get a specific text in a range of cells to blink

    Sorry to take up your precious time. Yes, I notice that the same words ("Expiring soon") don't blink at the same time. The number of records is around 20. Actually, using conditional formatting to highlight the words in red would suffice. But I just wanted to draw a bit more attention to the words appearing, and that is why I wanted to have the blinking effect. If you have the time and can figure out how to make them blink at the same time, it will be a bonus for me. I just need them to blink and there is no need to stop the blinking until the workbook is closed. Thank you again for your help. KS Chan.

  7. #7
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Can't get a specific text in a range of cells to blink

    Why is this posted in the Office 365 forum? The OP uses 2007, as per the profile. Please move to the general forum.

  8. #8
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Can't get a specific text in a range of cells to blink

    Good afternoon KSChan

    This thread has been moved to the Excel General forum as it seems unrelated to Excel 365, and your profile shows you as using Excel 2007.
    If this is no longer correct, then please take a moment to change it.

    Thanks

    DominicB

  9. #9
    Registered User
    Join Date
    04-14-2018
    Location
    Vermont, USA
    MS-Off Ver
    Office 365
    Posts
    72

    Re: Can't get a specific text in a range of cells to blink

    I think this one does it. Frankly, it would drive me crazy to have my workbook blinking at me constantly.

    You can edit column A while the macro runs, but if you enter 1 to 15, the corresponding cell will not join the blinking until you stop the macro and restart it.

    I had some problems with the Open event because in some cases the workbook opens but doesn't display until the macro ends. (You'll need one of the smart ones here to fix that!) The Close event stops the macro and restores the black font to column B.

    I added a button on the sheet to run the Blinker procedure and I changed the "escape" cell to "B1". The number of blinks is still 10 off/10 on, and the duration of each blink is still 1/2 second. You can adjust as needed.

    Good luck with it.

    - Jeff
    Attached Files Attached Files
    Last edited by Jeff Ho; 05-28-2018 at 10:35 AM.

  10. #10
    Registered User
    Join Date
    04-14-2018
    Location
    Vermont, USA
    MS-Off Ver
    Office 365
    Posts
    72

    Re: Can't get a specific text in a range of cells to blink

    Here's a work around: I added a msgbox on start up so you can decide whether to run the blinking or not. Stopping the Open event for user input gives it the break it needs for the workbook to display before running the macro. If you don't want users to opt out of the blinking, you can change the msgbox button to vbOkOnly.

    Please Login or Register  to view this content.
    Last edited by Jeff Ho; 05-28-2018 at 01:12 PM.

  11. #11
    Registered User
    Join Date
    10-06-2012
    Location
    Singapore
    MS-Off Ver
    Excel 365
    Posts
    50

    Re: Can't get a specific text in a range of cells to blink

    Thanks a lot Jeff for your time. I'll look at what you suggested. Perhaps I should leave the blinking text out for the time being.
    Hi Dominicb - when I joined this forum, I was using Excel 2007. But since last year, I've been using MS Office 365. I suppose the Excel is also Excel 365.
    Anyway, thanks to everyone. KS Chan

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Searching cells within a range that contain specific text
    By JohnG73 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-20-2016, 05:52 AM
  2. Search for a non-specific text in range of cells
    By Laatte in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-18-2016, 04:17 PM
  3. [SOLVED] VBA to add text into a range of cells if another cell has a specific value
    By steve64 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-21-2016, 03:15 PM
  4. [SOLVED] Return specific text if a different text is contained anywhere in a range of cells
    By ciayers in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2013, 05:54 PM
  5. Make specific cells blink
    By questionguy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-22-2012, 10:04 AM
  6. Replies: 7
    Last Post: 07-30-2012, 01:31 PM
  7. How to filter a range of cells for specific text?
    By talk2mide in forum Excel General
    Replies: 2
    Last Post: 03-01-2007, 05:35 PM

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