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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,868
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
50,868
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
50,868
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,756
Messages
5,574,050
Members
412,565
Latest member
roberttaekim
Top