VBA - Find all the White Cells and populate them with a Random Value.

Tim77

New Member
Joined
Jan 25, 2020
Messages
6
Office Version
2013, 2007
Platform
Windows
Hi all,

I am Junior on VBA and I can not make it with the below task. I have a huge worksheet with White and Grey Cells. My task is to find all the white Cells and Populate them with a Random value. Can you advise?

Thanks in advance,
 

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

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
686
Office Version
365, 2010, 2007
Platform
Windows, Mobile, Web
Like this? Keep in mind this will only do for a block of cells having format or value based on UsedRange
If you want exact range change to UsedRange to Range("range")
VBA Code:
Sub Tim77(Optional ws As Worksheet)

  Const bkColor2Find = vbWhite  ' Back color to find
  Const rndLB = 1               ' Lower bound for random number
  Const rndUB = 1000            ' Upper bound for random number
  If ws = Nothing Then _
    Set ws = activeworksheet
  For Each Cell In ws.UsedRange.Cells
    If cColor(Cell) = bkColor2Find Then
      Application.EnableEvents = False
      Cell.Value = Int((rndUB - rndLB + 1) * Rnd() + rndLB)
      Application.EnableEvents = True
    End If
  Next
  Set ws = Nothing
End Sub

Public Function cColor(Target As Range) As Long  'This will return the color of a cell, or the first color of a range.
  cColor = Target.Interior.color
End Function
 

Tim77

New Member
Joined
Jan 25, 2020
Messages
6
Office Version
2013, 2007
Platform
Windows
Like this? Keep in mind this will only do for a block of cells having format or value based on UsedRange
If you want exact range change to UsedRange to Range("range")
VBA Code:
Sub Tim77(Optional ws As Worksheet)

  Const bkColor2Find = vbWhite  ' Back color to find
  Const rndLB = 1               ' Lower bound for random number
  Const rndUB = 1000            ' Upper bound for random number
  If ws = Nothing Then _
    Set ws = activeworksheet
  For Each Cell In ws.UsedRange.Cells
    If cColor(Cell) = bkColor2Find Then
      Application.EnableEvents = False
      Cell.Value = Int((rndUB - rndLB + 1) * Rnd() + rndLB)
      Application.EnableEvents = True
    End If
  Next
  Set ws = Nothing
End Sub

Public Function cColor(Target As Range) As Long  'This will return the color of a cell, or the first color of a range.
  cColor = Target.Interior.color
End Function
Thanks for your reply. The compiler Stops at the For Statement/Line :/
 

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
686
Office Version
365, 2010, 2007
Platform
Windows, Mobile, Web
Thanks for your reply. The compiler Stops at the For Statement/Line :/
Just needed a Dim line as you must have Option Explicit above :)
And my ws line had a mistake below are the corrections...

VBA Code:
Sub Tim77(Optional ws As Worksheet)
  Dim Cell As Range
  Const bkColor2Find = vbWhite  ' Back color to find
  Const rndLB = 1               ' Lower bound for random number
  Const rndUB = 1000            ' Upper bound for random number
  If ws = Nothing Then _
    Set ws = Application.ActiveSheet
  For Each Cell In ws.UsedRange.Cells
    If cColor(Cell) = bkColor2Find Then
      Application.EnableEvents = False
      Cell.Value = Int((rndUB - rndLB + 1) * Rnd() + rndLB)
      Application.EnableEvents = True
    End If
  Next
  Set ws = Nothing
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,667
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about
Rich (BB code):
Sub Tim77()
   Dim Cl As Range
   Const MinNum As Long = 1
   Const MaxNum As Long = 1000
   
   For Each Cl In Range("A2:X100")
      If Cl.Interior.Color = vbWhite Then
         Cl.Value = Int((MaxNum - MinNum + 1) * Rnd() + MinNum)
      End If
   Next Cl
End Sub
Change the range in red and the Min/Max numbers in blue to suit
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,667
Office Version
365
Platform
Windows
@CSmith
You will also get a compile error on this If ws = Nothing Then
 

Tim77

New Member
Joined
Jan 25, 2020
Messages
6
Office Version
2013, 2007
Platform
Windows
Thanks both for your time.On Fluff's proposal the compilor stops at "
Cl.Value = Int((MaxNum - MinNum + 1) * Rnd() + MinNum)"
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,908
Office Version
2010
Platform
Windows
Hi & welcome to MrExcel.
How about
Rich (BB code):
Sub Tim77()
   Dim Cl As Range
   Const MinNum As Long = 1
   Const MaxNum As Long = 1000
  
   Randomize

   For Each Cl In Range("A2:X100")
      If Cl.Interior.Color = vbWhite Then
         Cl.Value = Int((MaxNum - MinNum + 1) * Rnd() + MinNum)
      End If
   Next Cl
End Sub
Change the range in red and the Min/Max numbers in blue to suit
Since you are using the Rnd function, you will need to add the Randomize statement I show in red above, otherwise the Rnd function will generate the same set of values each time the Workbook is opened. Alternately, you could omit the Randomize statement and change the line of code I highlighted in blue above to this...

Cl.Value = Application.RandBetween(MinNum, MaxNum)
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,908
Office Version
2010
Platform
Windows
Thanks both for your time.On Fluff's proposal the compilor stops at "
Cl.Value = Int((MaxNum - MinNum + 1) * Rnd() + MinNum)"
Fluff's code does not have a quote mark where you are showing it.... remove that quote mark and the line of code should work.

*** Make sure you read what I posted in Message #8 above. ***
 

Tim77

New Member
Joined
Jan 25, 2020
Messages
6
Office Version
2013, 2007
Platform
Windows
Thank you! I faced a Run-time error '1004'. Due to protection on the Sheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,063
Messages
5,466,370
Members
406,478
Latest member
Amar kumar

This Week's Hot Topics

Top