Display the result in the active cell in another cell

[rvan07]

Hello and thank you for any help.
The task basically involves two cells. C2 with value 6. C4 with value 9.
Task: When C2 is active, it's value should appear in C4 and C4 will also turn yellow.
When C2 is not active, the value in C4 will be 9 and will not be yellow.
The following code partially works.
It does not allow C4 to have a value when C2 is not active. May need to re-write line 3.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range
Range("C4").ClearContents
If Not Intersect(Target, Range("C2")) Is Nothing Then
For Each c In Intersect(Target, Range("C2"))
If c.Address = ActiveCell.Address Then
Range("C4").Value = c.Value
Exit For
End If
Next c
End If
End Sub

Thank you,

 

[Joe4]

Welcome to the Board!

You have some discrepancies in your explanation (not sure if you want the VBA code to set it to hard-coded values, or the values of other cells, or a combination of both.
In any event, I have documented the code so you should be able to easily follow along and make changes where necessary:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'   If cell C2 is the activecell
    If ActiveCell.Address = "$C$2" Then
'       Set C4 equal to C2
        Range("C4").Value = Range("C2").Value
'       Turn cell yellow
        Range("C4").Interior.Color = 65535
'   If cell C2 is not the activecell
    Else
'       Set C4 equal to 9
        Range("C4").Value = 9
'       Remove color from cell C4
        Range("C4").Interior.Pattern = xlNone
    End If
       
End Sub

I also removed your email address from your initial post.
You should NEVER post your email address in public user forums, as Spambots routinely patrol forums such as these looking for email address to gather and Spam!

 

[rvan07]

Welcome to the Board!

You have some discrepancies in your explanation (not sure if you want the VBA code to set it to hard-coded values, or the values of other cells, or a combination of both.
In any event, I have documented the code so you should be able to easily follow along and make changes where necessary:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'   If cell C2 is the activecell
    If ActiveCell.Address = "$C$2" Then
'       Set C4 equal to C2
        Range("C4").Value = Range("C2").Value
'       Turn cell yellow
        Range("C4").Interior.Color = 65535
'   If cell C2 is not the activecell
    Else
'       Set C4 equal to 9
        Range("C4").Value = 9
'       Remove color from cell C4
        Range("C4").Interior.Pattern = xlNone
    End If
      
End Sub

I also removed your email address from your initial post.
You should NEVER post your email address in public user forums, as Spambots routinely patrol forums such as these looking for email address to gather and Spam!

Wow! Thankyou Joe4, it works perfectly. Thats totally awesome. And thank you also for removing my e-address

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[rvan07]

Wow! Thankyou Joe4, it works perfectly. Thats totally awesome. And thank you also for removing my e-address

My intent was to extend the code in my application to incorporate other pairs of cells in the same manner but alas, the solution is not obvious.
Task: On the same worksheet, D3 value is 6. D5 value is 9.
When D3 is active, it's value should appear in D5 and D5 interior should turn yellow.
When D3 is not active, D5 value is 9 and not yellow.
The task would repeat yet again: E4 value is 6. E6 value is 9.
The code allowing this event to work should get me on my way.
In total, my application will require 108 repeats.
Thank you again for any help.
rvan

 

[Joe4]

In total, my application will require 108 repeats.

A good rule of thumb here. NEVER oversimplify your question when posting here. If you do, you may get the correct answer to the question you posted, but it really doesn't solve your full, underlying problem. Unless you feel 100% confident that you can take the answer and extend it out on your own, it is best to post the full nature/extent of your issue.

Is there any "pattern" to this 108 cells you want to apply this to? If not, it is going to be VERY cumbersome, as you are going to need to explicitly have 108 cell references in your code!

 

[rvan07]

Thank you for your support and sound guidance in approaching a problem of this nature.
I am rethinking how best to pose the problem from the perspective of repetitive patterns.