# Office 365 >  >  Can't get a specific text in a range of cells to blink

## KSChan

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

----------


## Jeff Ho

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.

----------


## KSChan

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.

----------


## KSChan

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.

----------


## Jeff Ho

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

----------


## KSChan

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.

----------


## teylyn

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

----------


## dominicb

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

----------


## Jeff Ho

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

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.  :Wink: 

- Jeff

----------


## Jeff Ho

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.
```

----------


## KSChan

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

----------

