Format cells from random numbers

[alfaspud]

Hi

I am trying to work out a way of conditionally formatting individual cells which are determined randomly when a spreadsheet is opened

I am using RANDBETWEEN to pick 5 numbers, eg. 3, 5, 18, etc
I then want to conditionally format cells C3, C5, C18

Is this possible using formula or do I need VBA?
Is it possible to insert a value into these cells by combining "c" with the random number?

I've been playing around with INDIRECT and CELL in conditional formatting formulas but can't seem to get anything to work

Thanks

 

[footoo]

I think you need VBA.

What cells contain the random numbers?
Are the numbers fixed or dependent upon RANDBETWEEN and, therefore, volatile?
How do want to format the column C random cells?
What about previous column C values/formatting - should column C be cleared before identifying the new random cells?
What values to be put in the column C cells?

 

[alfaspud]

Hi

Cells a1 to a5 contain 5 random numbers generated using RANDBETWEEN(1,35)
These are generated the first time the sheet is opened - they remain the same afterwards (not recalculated)
I just want to format the fill (background) of the corresponding cells in column C
If i use a formula in column B to create a cell ref (="C"&A1), can I then use the cell ref in a conditional formatting formula?

Alternatively, can I place a value (number or text) in the cell referred to? i could then use this value to conditionally format the cell?

 

[Marcelo Branco]

Maybe...

Select C1:C35
In Conditional Formatting try this formula
=ISNUMBER(MATCH(ROWS(C$1:C1),A$1:A$5,0))

pick the format you want.

M.

 

[footoo]

Cells a1 to a5 contain 5 random numbers generated using RANDBETWEEN(1,35)
These are generated the first time the sheet is opened - they remain the same afterwards (not recalculated)

How is this done? By a Workbook_Open procedure ?

Alternatively, can I place a value (number or text) in the cell referred to? i could then use this value to conditionally format the cell?

Putting a value in the cells would need VBA, so instead could just highlight the cells with VBA and clear previous highlights (and forget about putting a value and CF) .

I have to leave the forum now - will re-visit in about 12 hours.

 

[alfaspud]

Perfect Marcelo

Thank you

 

[Marcelo Branco]

Perfect Marcelo

Thank you

You are welcome. Thanks for the feedback.

M.

 

[alfaspud]

Hi footoo
Marcelo's solution worked for me
If you have time to post a VBA solution I would be very interested too - I am only learning the basics of VBA so any insight is good
If not, no worries
Thanks for your interest

 

[Rick Rothstein]

If you have time to post a VBA solution I would be very interested too - I am only learning the basics of VBA so any insight is good

Not sure this is a good piece of code to learn from as it uses some more advanced techniques, but I'll post it anyway...

[C1:C35].Interior.ColorIndex = xlNone
Range("C" & Join([TRANSPOSE(A1:A5)], ",C")).Interior.Color = vbYellow

 

[footoo]

A less advanced, easier to understand, code to highlight the column C cells (should be no noticeable difference in the run-time compared with Rick Rothstein's code):

Sub Highlight()
Dim cel As Range
[C1:C35].Interior.Color = xlNone
For Each cel In [A1:A5]
    Cells(cel, "C").Interior.Color=vbYellow
Next
End Sub

You didn't mention how and when you get the random numbers in A1:A5.
Here's a macro that puts new unique numbers in A1:A5 and highlights the corresponding column C cells:

Sub Rand_Highlight()
Dim RndNbrs As Collection, i%
Set RndNbrs = New Collection
Randomize
On Error Resume Next
Do
    i = CLng(Rnd * 34 + 1)
    RndNbrs.Add i, CStr(i)
Loop Until RndNbrs.Count = 5
On Error GoTo 0
[C1:C35].Interior.Color = xlNone
For i = 1 To 5
    Cells(i, "A").Value = RndNbrs(i)
    Cells(RndNbrs(i), "C").Interior.Color = vbYellow
Next i
End Sub

The macros could run automatically by being called by an appropriate event procedure.
For example, for any change of value in A1:A5 (other than by a formula):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
If Intersect(Target, [A1:A5]) Is Nothing Then Exit Sub
Call Highlight
End Sub

Or, for example, the Rand_Highlight macro could be called from a Workbook_Open procedure.
If you want to automate any of the code but are not sure how to do it, post again.