YES, formulas CAN change values in OTHER cells!

Okay, one thing we have been told over and over again is that a formula, even a UDF (user defined formula) cannot put a value into any cell other than the one it is in. Well, it seems that is not entirely true... there is a way to make a UDF do just that. Yesterday I posted a method which allows a UDF to display the color value of any specified cell (manually set or by means of Conditional Formatting). Although I posted it to several Excel groups, here is the link to my LinkedIn feed posting...

https://www.dhirubhai.net/posts/rick-rothstein-a8b814201_excel-vba-user-defined-function-that-counts-activity-6861066320809639936-wxhg

It seems the structure of the functions that let this happen can also be used to put values into other cells! Copy/paste the following two functions into a general code module...

Function PushValue(ByVal Rng As Range, V As Variant) As Double
? Evaluate "Helper(" & Rng.Address & "," & V & ")"
End Function

Private Function Helper(ByVal R As Range, VV As Variant) As Variant
? R.Value = VV
End Function        

Now put this formula in cell A1...

=PushValue(C3,999)

and look at what is displayed in cell C3. Surprised? Edit the formula to read this way...

=PushValue(C3,"""Hello""")

Notice the value was updated to now say Hello. Note that text must be quoted so that the text with attached quote marks will be passed to the Helper function (hence the three leading and trailing quotes). Now this can be generalized so that the cell and value can be specified in cells on the worksheet. Delete the two functions above and replace them with these...

Function PushValue(ByVal Addr As String, V As Variant) As String
? Static Cnt As Long
? If Cnt > 2 Then Cnt = 0
? Cnt = Cnt + 1
? If Cnt = 2 Then
? ? Cnt = 0
? ? Evaluate "Helper(""" & Addr & """," & V & ")"
? End If
? PushValue = "Enter Address and Value ==>"
End Function

Private Function Helper(ByVal Raddr As String, Var As Variant) As Variant
? Range(Raddr).Value = Var
End Function        

You will need to widen Column A so that the entire message in cell A1 can be seen. Put this formula into cell A1...

=PushValue(B1,C1)

Okay, now type a cell address (other than A1 of course) into cell B1 and a value into cell C1 (text now only have to be quoted normally). Okay, now type a different address and a different value into cells B1 and C1. You can, of course, keep doing this as much as you like. So there you go... contrary to established dogma, a function can indeed change a value in a different cell.

Am I done yet? Nope! Let's really blow your mind. I'll go back to the simpler construction shown at the beginning and let you modify it for the more generalize procedures I showed after. Replace the two procedures with these...

Function ChangeColor(ByVal Rng As Range, ColorVal As Long) As String
? Evaluate "Helper(" & Rng.Address & "," & ColorVal & ")"
End Function

Private Function Helper(ByVal R As Range, CV As Long) As Variant
? R.Interior.Color = CV
End Function        

and put this formula in, say, cell A1...

=ChangeColor(B1,255)

Cell B1 changed to red because 255 is the color value for red. Change the value from 255 to 65280 and cell B1 will change to green.

Okay, so is your mind blown?