Changing cell color randomly for counter increment through VBA Loop

ChauhanSaab

New Member
Joined
May 5, 2020
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi,

For the below goal seek macro, the counter cell AF1 changes on every iteration.
Can i add some code to change the color of the cell randomly on every counter increment till the loop completes.

Sub Goal_Seek_Range()
Dim j As Integer
Dim count As Long
count = 0

For j = 5 To 2482
Cells(j, "AH").GoalSeek Goal:=135, ChangingCell:=Cells(j, "AG")
count = count + 1
Range("AF1").Value = count
DoEvents

Next j
ActiveWorkbook.Save
End Sub
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,062
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How long does it take to run that code?
 

ChauhanSaab

New Member
Joined
May 5, 2020
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
It takes around 2 hours since the base file contains many formulas that gets dynamically updated.
Mostly I run this macro before going to bed and by morning the run gets completed.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,062
Office Version
  1. 365
Platform
  1. Windows
Ok, you could use
VBA Code:
Range("AF1").Value = count
Range("AF1").Interior.Color = j * 10000
DoEvents
But change j from Integer to Long
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,062
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,118
Messages
5,576,198
Members
412,706
Latest member
msousa25
Top