0

I have 20+ sheets with the same structure and I am trying to make a summarization on the first sheet. The issue I am facing is that I am a complete rookie when it comes to how to use vba.

The code below is what I thought was the first step, all the cells from A2:A31 in blad5 should retain the background color into blad1 E2:E31, if I change the color in blad5 it would show take color on blad1 (All the cells in Blad5 A2:A31 have a green background color)

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Worksheets(Blad2).Range("A2:A31").Interior.Color = Worksheets(Blad1).Range("E2:E31").Interior.Color
 End Sub

My intention is to utilize a do loop over all the sheets (blad2-blad27) to blad1 which is my summarization sheet.

Would be grateful for a nudge in the right direction on how to do a function en do loop for the sheets (minus blad1 / sheet1) and how to properly write the interior.color macro.

With kind regards, Fredrik

Karthick Gunasekaran
  • 2,697
  • 1
  • 15
  • 25
Fredrik
  • 1

1 Answers1

0

For the loop over the sheets you can use following:

For i = 2 To 27

Worksheets("Blad" & i).Range("A2:A31").Interior.Color=Worksheets(Blad1).Range("E2:E31").Interior.Color

Next i
Pawels
  • 192
  • 2
  • 12
  • Hi, I am gettting a error 13 when trying to compile the macro. – Fredrik Nov 26 '18 at 07:47
  • How did you dim your variables? – Pawels Nov 26 '18 at 08:32
  • I haven't, I am just using the default dims from the sheets. The thing I explained which was pretty lackluster is the my summarziation sheet (blad1) has columns which correspond to blad2-blad27. So the second part Interior.Color=Worksheets(Blad1).Range("E2:E31") has to reflect this aswell. One question, if I rename the sheets will excel vba still understand blad1 aso? – Fredrik Nov 26 '18 at 08:40
  • If you want to be renaming the sheets it is better than to use the VBA aliases in codes (it is the part that is shown in the VBA project window before the sheet name in the brackets. If you want to have all used cells colored on the sheet you can use ActiveRange property like here before you use the colors: ActiveSheet.UsedRange.Select. If you want to clor just part that changes you can try to find last row and column like here: https://stackoverflow.com/questions/38882321/excel-vba-better-way-to-find-last-row – Pawels Nov 26 '18 at 14:55
  • And for the variables, set variables as a certain type at the beginning of the code like: Dim i As Integer. Error 13 type mismatch is a sign that the excel expects different datatype than the variable is used for. – Pawels Nov 26 '18 at 14:59