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

[Tim77]

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,

 

[CSmith]

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")

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]

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")

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]

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

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]

Hi & welcome to MrExcel.
How about

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]

@CSmith
You will also get a compile error on this If ws = Nothing Then

 

[Tim77]

Thanks both for your time.On Fluff's proposal the compilor stops at "
Cl.Value = Int((MaxNum - MinNum + 1) * Rnd() + MinNum)"

 

[Rick Rothstein]

Hi & welcome to MrExcel.
How about

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)

 

[Rick Rothstein]

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]

Thank you! I faced a Run-time error '1004'. Due to protection on the Sheet.