Changing the value of multiple cells by clicking on cell B4

[LondonTeacherUK]

Hi guys,

I'm wanting to click on B4 and to have cells B8 to B36 change to the following:

Value = "Operate video camera, record a detailed and informative audio commentary"
Color = RGB(255, 255, 0)

I've tried mashing together various solutions from different places but despite my best efforts, nothing seems to be working. If anyone could help me with this I would be very grateful!

Thanks

 

[FDibbins]

Why not just change 1 cell as needed, then copy/paste to the others?

 

[LondonTeacherUK]

Thanks for your reply Ford.

That solution won't work for what I intend the spreadsheet to do.

I'm trying to create an assessment sheet for teachers to complete. I want them to be able to click on a cell and then have a number of other cells automatically change their value to be the same.

For the time being, the original value and colour mentioned in my first post would do it.

I'd ideally like a macro solution.

Thanks.

 

[My Aswer Is This]

So what cell do you want to click on to activate the script and explain what cells you want this click to effect.
I can write you a script where if you double click on cell "A1" it will put that cells value in whatever cells you want.
Details please we always need details.

 

[My Aswer Is This]

After rereading your post #1 I see now this is what you want:
You will need to double click on cell "B4" to get the results you wanted.
To install this code:

Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("B4")) Is Nothing Then
Cancel = True
Range("B8:B36").Value = "Operate video camera, record a detailed and informative audio commentary"
Range("B8:B36").Interior.Color = RGB(255, 255, 0)
End If
End Sub

 

[My Aswer Is This]

In your post # 3 it sounds like this may be what you want.
Modify the ranges to meet your needs.
When you double click "B4" the script will fill the ranges with the value in cell "B4"

To install this code:

Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("B4")) Is Nothing Then
Cancel = True
Range("F8,L12,P8,Q15,H15,J5,M4,N16").Value = Range("B4").Value
End Sub

 

[LondonTeacherUK]

Awesome! Thanks 'My Answer Is This', it worked a treat

I've moved on to the following now. I have 4 different cells that I want to have the code that you posted above. I've done this and it works:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


Cancel = True


    If Not Intersect(Target, Range("B4")) Is Nothing Then
        Range("B8:B36").Value = Range("B4").Value
        Range("B8:B36").Interior.Color = Range("B4").Interior.Color
    End If


    If Not Intersect(Target, Range("B5")) Is Nothing Then
        Range("B8:B36").Value = Range("B5").Value
        Range("B8:B36").Interior.Color = Range("B5").Interior.Color
    End If


    If Not Intersect(Target, Range("B6")) Is Nothing Then
        Range("B8:B36").Value = Range("B6").Value
        Range("B8:B36").Interior.Color = Range("B6").Interior.Color
    End If


    If Not Intersect(Target, Range("B7")) Is Nothing Then
        Range("B8:B36").Value = Range("B7").Value
        Range("B8:B36").Interior.Color = Range("B7").Interior.Color
    End If
End Sub

It works fine but is this the best way to do it? Also, I have another 5 columns on this worksheet which I want to do exactly the same thing. User can choose between 4 different cells to automatically fill the range below - C4, C5, C6 or C7 to fill C8:C36, then same again in columns D, E and F.

Should I just copy the code and change the Range values? Or is there a better way of doing it?

Thank you so much for the help already! I can already see this saving our teaching staff so much time when it comes to assessment time.

 

[My Aswer Is This]

What is changing in each of these scripts? The values or the cell color or both.
I notice in your above scripts the Range("B8:B36") is always staying the same.
Is the value always a long amount of text like "Operate video camera, record a detailed and informative audio commentary"
Or something shorter.

If the different is only the text and if you wanted we could have an inputbox popup and you could put the text into the input box.

 

[LondonTeacherUK]

The value and cell colour are changing.

The range ("B8:B36") stays the same because that is filling in all of the cells which correspond to the pupils' names.

I have a separate piece of code which then lets me go in and change the cells in that range 1 by 1. The whole point is that the teacher can click on the cell which best matches the majority of the class. Then they can go in and change the higher and lower performing performing pupils individually. I'll try to give you an example:

Column B is assessing pupils' ability to work with various forms of input. Teachers have the following 4 options to choose from:

B4 = Operate video camera, record a detailed and informative audio commentary (yellow)
B5 = Operate video camera, record audio commentary (green)
B6 = Operate video camera (blue)
B7 = Didn't do (white)

Say the majority of the class matches B5, which is what we'd expect most of our pupils to do, they'd click on B5 and the range B8:B36 fills in with that value and colour. They can then go through and change the 5 or so pupils who achieved B4, and the 5 or so pupils who achieved B6.

The colour is used so we can quickly see the pupils who are meeting the minimum requirements, green, who are excelling, yellow, and who need addition support, blue or white.

Column C is then assessing the pupils' ability to Use software on a range of digital devices. The options for the teachers look like this:

C4 = Record high quality footage (yellow)
C5 = Record usable footage (green)
C6 = Record unusable footage (blue)
C7 = Didn't do (white)

Teachers would then go through the same process as before.

The end users, the teachers, will not need to enter in any text, colours or values. They will simply be choosing the best fit for their class and for each child. I will create the spread sheet and enter in all of the text myself.

Apologies for the lengthy description. I hope this makes it a bit clearer as to what I'm trying to achieve. If you have any more suggestions or ideas I would like to hear back from you.

 

[My Aswer Is This]

From what I could see from your previous code posting it looks like you have a good handle on this. I don't see any way you could do anything better. If you have any more specific needs check back in here and I will see if I can help you. Take care