Changing value of group of cells by one cell value change

C

csbaros

New Member
Joined
Apr 22, 2021
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
Dear Folks,

Please help me in the following:
There are couple of hundred units, dispersed randomly on the sheet. There is two different type of unit. (unit border marked by thick line).
Unit types:
- 3x27 cells unit: contains: 2x12 pcs of ⚪, as a default value
- 3x9 cells: contains: 2x5 pcs of ⚪, as a default value

Small part of the sheet:
1620317115803.png

Second cell in first and third row in every 3x27 unit contains a dropdown menu. (In the 3x9 unit the dropdown is in the first cell)
I would like reach, that the changing this dropdown will change the whole row, like this (change all ⚪ for ⚫ in the row).

1620318637116.png



And then, changing the 2nd cell of third row:
1620317655398.png

Or randomly choose any of the 1st or 3rd row in any unit in the sheet:

1620318108122.png


The xls is here: example.xlsx

I suppose, probably could be solved by a VBA code, but I don't have any clue about it.

Please help me. This would be almost the last step in my project. (If my description wasn't clear, I would try it again. If it need, I could upload the big sheet, not just this small one)

Thanks a lot

Csaba
 

Attachments

  • 1620317503772.png
    1620317503772.png
    6.7 KB · Views: 6
  • 1620317626909.png
    1620317626909.png
    221.9 KB · Views: 5
  • 1620317907795.png
    1620317907795.png
    6.9 KB · Views: 6
  • 1620318547880.png
    1620318547880.png
    6.7 KB · Views: 7

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
If you can upload your big sheet it might help to understand your description and logic.
 
Upvote 0
Thank you very much:

szolnok1_v3.xlsm

I just would like to switch between these ⚪/⚫ symbols not piece by piece from the dropdown, rather would like to change the whole row within the unit. (If I change piece by piece, that brings a lot of dropdown (data validation). It makes the computer very slow).

Please note, I have just planted in this phase this dropdown (data validation) in the first couple of rows. (On the very top of the uploaded full sheet).

I'm so sorry if I was too blurry. Please ask me if you need more info about the issue.

Thanks a lot
 
Upvote 0
I think I understand what you want to do. I need to know how to determine how wide a section is. So, for instance, if you change cell 2 in the first row, how will I know where to stop changing the others? You have cell border weights changed but that is strictly a visual cue, not a reliable programtic cue.

This looks like it might be some map or representation of store shelving or warehouse. Was it all setup manually in the sheet? I am looking for some way to think about the layout that help me determine how to programmatically manipulate the design to get what you want.
 
Upvote 0
This is a layout of a photovoltaic plant. I've copied it from its design drawing. These units represent two types solar tables. The full table contains 3x27 solar modules, the small one 3x9 pieces. The location of the tables (units) are random.

Yes. I think, I understand your point.
We could use the second character of value of first cell in the second row to determine or choose between the two type of unit.
It is always F (Full) in case of big units and it is always S (Small) in case of the small ones.

1620383922241.png


Thank you again for your support
 
Upvote 0
I have taken your workbook code and worked out something to answer your original question. I will post it below. In examining your workbook, I was trying to workout how I might help you with its design. I have some ideas but needed to now what the function of this workbook is to be. Is it some kind of interactive map? I noticed there about four different layouts. Are they different plants?

My goal is to help you create a future usable workbook that optimizes what seems to me to be a user interface/user experience effort. I am going to play around with some ideas I have and will pass them on to you for review. My hope is that I can help you learn some more about VBA and programming in general.

Here is the code:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim clr As Long, blk As Long, rng As Range
    Dim r As Long, c As Long
    Dim TargetRow As Long, TargetColumn As Long
    Dim UnitTypeCodeCell As Range, arr As Variant
    
    If Target.CountLarge > 1 Then Exit Sub
    
    Application.EnableEvents = False
    TargetRow = Target.Row
    TargetColumn = Target.Column
    Set UnitTypeCodeCell = Cells(TargetRow + 1, TargetColumn - 1)
    If Not (UnitTypeCodeCell.Value = "0S" Or UnitTypeCodeCell = "0F") Then
      Set UnitTypeCodeCell = Cells(TargetRow - 1, TargetColumn - 1)
    End If
    If Not (UnitTypeCodeCell.Value = "0S" Or UnitTypeCodeCell = "0F") Then
      Exit Sub
    End If
    
    Select Case UnitTypeCodeCell
        Case "0S"
            clr = 16777215
            blk = 8
            arr = Array(2, 4, 6, 8)
        Case "1S"
            clr = 65535
            blk = 8
        Case "2S"
            clr = 49407
            blk = 8
        Case "3S"
            clr = 5287936
            blk = 8
        Case "4S"
            clr = 12611584
            blk = 8
        Case "0F"
            clr = 16777215
            blk = 26
            arr = Array(2, 4, 6, 9, 11, 13, 15, 18, 20, 22, 24)
        Case "1F"
            clr = 65535
            blk = 26
        Case "2F"
            clr = 49407
            blk = 26
        Case "3F"
            clr = 5287936
            blk = 26
        Case "4F"
            clr = 12611584
            blk = 26
        Case "0_C"
            clr = 16777215
            blk = 2
        Case "1_C"
            clr = 65535
            blk = 2
        Case "2_C"
            clr = 49407
            blk = 2
        Case "3_C"
            clr = 5287936
            blk = 2
        Case "4_C"
            clr = 12611584
            blk = 2
        Case "0_S"
            clr = 16777215
            blk = 1
        Case "1_S"
            clr = 65535
            blk = 1
        Case "2_S"
            clr = 49407
            blk = 1
        Case "3_S"
            clr = 5287936
            blk = 1
        Case "4_S"
            clr = 192
            blk = 1
        Case "5_S"
            clr = 12611584
            blk = 1
            
    End Select
    
    If blk > 0 Then
      r = Target.Row
      c = Target.Column
      Select Case blk
        Case 1
          Set rng = Range(Cells(r, c), Cells(r + 2, c + 8))
          rng.Interior.Color = clr
        Case 2
          Set rng = Range(Cells(r, c), Cells(r, c))
          rng.Interior.Color = clr
        Case Else ' > 2 '
'          Set rng = Range(Cells(r - 1, c), Cells(r + 1, c - 1 + blk))
'          rng.Interior.Color = clr
          For i = LBound(arr) To UBound(arr)
              Cells(TargetRow, TargetColumn + arr(i)) = Target.Value
          Next i
      End Select
    Else
      ' blk is LE 0
    End If
    Application.EnableEvents = True
End Sub

Be sure to asked questions to gain understanding. Note my signature line below.
 
Upvote 0
vw412 said:
I have taken your workbook code and worked out something to answer your original question. I will post it below. In examining your workbook, I was trying to workout how I might help you with its design. I have some ideas but needed to now what the function of this workbook is to be. Is it some kind of interactive map? I noticed there about four different layouts. Are they different plants?

My goal is to help you create a future usable workbook that optimizes what seems to me to be a user interface/user experience effort. I am going to play around with some ideas I have and will pass them on to you for review. My hope is that I can help you learn some more about VBA and programming in general.

Here is the code:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim clr As Long, blk As Long, rng As Range
    Dim r As Long, c As Long
    Dim TargetRow As Long, TargetColumn As Long
    Dim UnitTypeCodeCell As Range, arr As Variant
   
    If Target.CountLarge > 1 Then Exit Sub
   
    Application.EnableEvents = False
    TargetRow = Target.Row
    TargetColumn = Target.Column
    Set UnitTypeCodeCell = Cells(TargetRow + 1, TargetColumn - 1)
    If Not (UnitTypeCodeCell.Value = "0S" Or UnitTypeCodeCell = "0F") Then
      Set UnitTypeCodeCell = Cells(TargetRow - 1, TargetColumn - 1)
    End If
    If Not (UnitTypeCodeCell.Value = "0S" Or UnitTypeCodeCell = "0F") Then
      Exit Sub
    End If
   
    Select Case UnitTypeCodeCell
        Case "0S"
            clr = 16777215
            blk = 8
            arr = Array(2, 4, 6, 8)
        Case "1S"
            clr = 65535
            blk = 8
        Case "2S"
            clr = 49407
            blk = 8
        Case "3S"
            clr = 5287936
            blk = 8
        Case "4S"
            clr = 12611584
            blk = 8
        Case "0F"
            clr = 16777215
            blk = 26
            arr = Array(2, 4, 6, 9, 11, 13, 15, 18, 20, 22, 24)
        Case "1F"
            clr = 65535
            blk = 26
        Case "2F"
            clr = 49407
            blk = 26
        Case "3F"
            clr = 5287936
            blk = 26
        Case "4F"
            clr = 12611584
            blk = 26
        Case "0_C"
            clr = 16777215
            blk = 2
        Case "1_C"
            clr = 65535
            blk = 2
        Case "2_C"
            clr = 49407
            blk = 2
        Case "3_C"
            clr = 5287936
            blk = 2
        Case "4_C"
            clr = 12611584
            blk = 2
        Case "0_S"
            clr = 16777215
            blk = 1
        Case "1_S"
            clr = 65535
            blk = 1
        Case "2_S"
            clr = 49407
            blk = 1
        Case "3_S"
            clr = 5287936
            blk = 1
        Case "4_S"
            clr = 192
            blk = 1
        Case "5_S"
            clr = 12611584
            blk = 1
           
    End Select
   
    If blk > 0 Then
      r = Target.Row
      c = Target.Column
      Select Case blk
        Case 1
          Set rng = Range(Cells(r, c), Cells(r + 2, c + 8))
          rng.Interior.Color = clr
        Case 2
          Set rng = Range(Cells(r, c), Cells(r, c))
          rng.Interior.Color = clr
        Case Else ' > 2 '
'          Set rng = Range(Cells(r - 1, c), Cells(r + 1, c - 1 + blk))
'          rng.Interior.Color = clr
          For i = LBound(arr) To UBound(arr)
              Cells(TargetRow, TargetColumn + arr(i)) = Target.Value
          Next i
      End Select
    Else
      ' blk is LE 0
    End If
    Application.EnableEvents = True
End Sub

Be sure to asked questions to gain understanding. Note my signature line below.
Click to expand...
Really thank you very much for your help.

This a kind of interactive map. The different colors represent different work phases in the construction process. And since this is a big area (80-100 Hectars) , it gives a good overview (optical and quantity related) of the current status of the construction.
The 4 different layout belongs to one power plant, but they are located separately.

I tried your code. My experience is:
- it works in the big unit at "0F" setting, but if change for "1F" or "2F" it stop to work
- in the small units doesn't work
- the whole sheet lost his functionality regarding changing its color by changing the first cell of the second row. (like in the uploaded sheet).
I tried to fix it, but I couldn't manage it.
May I ask you further help ?

In long term, I think about to convert this excel sheet into some software. But first I would like to test this excel sheet on construction site, during the work. With the collected experience go further.
Do you interested this kind of work ?
 
Upvote 0
As I was examining your code I saw all the 1F, 2F, etc references, but didn't know what they meant. I commented out the color change function to test the changing first cell.

I will be glad to help you with this. I enjoy working on User Interface problems in Excel.
 
Upvote 0
I found the problem with the short tables not working. Here is the updated code:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim clr As Long, blk As Long, rng As Range
    Dim r As Long, c As Long
    Dim TargetRow As Long, TargetColumn As Long
    Dim UnitTypeCodeCell As Range, arr As Variant
    
    If Target.CountLarge > 1 Then Exit Sub
    
    Application.EnableEvents = False
    TargetRow = Target.Row
    TargetColumn = Target.Column
    If Cells(TargetRow + 1, TargetColumn - 1).Value = "0F" Then
      Set UnitTypeCodeCell = Cells(TargetRow + 1, TargetColumn - 1)
    ElseIf Cells(TargetRow - 1, TargetColumn - 1).Value = "0F" Then
      Set UnitTypeCodeCell = Cells(TargetRow - 1, TargetColumn - 1)
    ElseIf Cells(TargetRow + 1, TargetColumn).Value = "0S" Then
      Set UnitTypeCodeCell = Cells(TargetRow + 1, TargetColumn)
    ElseIf Cells(TargetRow - 1, TargetColumn).Value = "0S" Then
      Set UnitTypeCodeCell = Cells(TargetRow - 1, TargetColumn)
    Else
      Application.EnableEvents = True
      Exit Sub
    End If
    
    Select Case UnitTypeCodeCell
        Case "0S"
            clr = 16777215
            blk = 8
            arr = Array(2, 4, 6, 8)
        Case "1S"
            clr = 65535
            blk = 8
        Case "2S"
            clr = 49407
            blk = 8
        Case "3S"
            clr = 5287936
            blk = 8
        Case "4S"
            clr = 12611584
            blk = 8
        Case "0F"
            clr = 16777215
            blk = 26
            arr = Array(2, 4, 6, 9, 11, 13, 15, 18, 20, 22, 24)
        Case "1F"
            clr = 65535
            blk = 26
        Case "2F"
            clr = 49407
            blk = 26
        Case "3F"
            clr = 5287936
            blk = 26
        Case "4F"
            clr = 12611584
            blk = 26
        Case "0_C"
            clr = 16777215
            blk = 2
        Case "1_C"
            clr = 65535
            blk = 2
        Case "2_C"
            clr = 49407
            blk = 2
        Case "3_C"
            clr = 5287936
            blk = 2
        Case "4_C"
            clr = 12611584
            blk = 2
        Case "0_S"
            clr = 16777215
            blk = 1
        Case "1_S"
            clr = 65535
            blk = 1
        Case "2_S"
            clr = 49407
            blk = 1
        Case "3_S"
            clr = 5287936
            blk = 1
        Case "4_S"
            clr = 192
            blk = 1
        Case "5_S"
            clr = 12611584
            blk = 1
            
    End Select
    
    If blk > 0 Then
      r = Target.Row
      c = Target.Column
      Select Case blk
        Case 1
          Set rng = Range(Cells(r, c), Cells(r + 2, c + 8))
          rng.Interior.Color = clr
        Case 2
          Set rng = Range(Cells(r, c), Cells(r, c))
          rng.Interior.Color = clr
        Case Else ' > 2 '
'          Set rng = Range(Cells(r - 1, c), Cells(r + 1, c - 1 + blk))
'          rng.Interior.Color = clr
          For i = LBound(arr) To UBound(arr)
              Cells(TargetRow, TargetColumn + arr(i)) = Target.Value
          Next i
      End Select
    Else
      ' blk is LE 0
    End If
    Application.EnableEvents = True
End Sub
Note, the color change code is still commented. This also won't change anything if it is "1F","2F","1S","2S", etc. Need more discussion about this.
 
Upvote 0
Solution
Yes, not it changes the small ones. Perfect. Thanks a lot.

It is enough when it change these circles at "0F" or "0S" and at another settings, ie. "1F" or "2F" it keep the value. I was wrong yesterday, when I wanted to change them by another values. Sorry about that. Now it is very ok, doesn't need to change anything regarding this.

Only one more last thing: I tried to set back the color setting function with deleting comment sign, but it doesn't work for me. Sorry, but I'm really not a VBA expert. Would you be so kind and set that back for me ?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Create new sheets based on criteria in a table and value in a cell
Replies
6
Views
270
Herakles
H
C
  • Question
Cursor not changing from '+' to 'hand' on a couple of random cells with hyperlinks
Replies
0
Views
90
centurymantra
C
C
  • Question
Finding any two values in each cell text in a column of cells with both values as one Textbox1 variable
2
Replies
10
Views
210
chazrab
C
C
  • Solved
Group of cells background colouring using VBA
Replies
5
Views
390
GWteB
GWteB
A
  • Question
Lookup value based on multiple criteria where one is searching a cell for specific text string
Replies
0
Views
228
alex0182828
A

Forum statistics

Threads
1,214,522
Messages
6,120,020
Members
448,938
Latest member
Aaliya13

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top