VBA code to run over many rows and show shapes if the area is being used,

[richie247]

I have started writing a VBA code to check and see if an area is being used, (I have 52 areas) I have used a formula to see if it is true. the if it is true I get I to check another cell to see what the area is used for then change the color to the specified. the data could be a short as 10 lines on quite days and over 100 on busy days.

I think if I was to write the code it would take a long time having to change the cell references for every line.

here is a sample of the code i am using which works, i just need to try and simplify it

Sub ShowHideAreas()

If Worksheets("Sheet2").Range("G2").Value = True Then
Worksheets("Sheet1").Shapes.Range(Array("Area_A1")).Visible = msoTrue
Else
Worksheets("Sheet1").Shapes.Range(Array("Area_A1")).Visible = msoFalse
End If
If Worksheets("Sheet2").Range("G2").Value = True Then

If Worksheets("Sheet2").Range("C2") = "Yellow" Then
Worksheets("Sheet1").Shapes.Range(Array("Area_A1")).Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 255, 0)
Else
If Worksheets("Sheet2").Range("C2") = "Blue" Then
Worksheets("Sheet1").Shapes.Range(Array("Area_A1")).Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(51, 102, 255)
Else
If Worksheets("Sheet2").Range("C2") = "Red" Then
Worksheets("Sheet1").Shapes.Range(Array("Area_A1")).Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
Else
If Worksheets("Sheet2").Range("C2") = "Lavendar" Then
Worksheets("Sheet1").Shapes.Range(Array("Area_A1")).Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(204, 153, 255)

End If
End If
End If
End If
End If
End sub

the range for each area will be from G2:BM2 (all shapes area free formed and name changed to Area_xx)then down and the cell for the use of the area will be C2:C:100.

Many thanks in advance

 

[richie247]

Many thanks for your help in simplifying the code,

it works perfectly

regards

Richie

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[richie247]

i have been asked to show what areas are in use during different times. i have taken the times out of the cells and converted them into time format and used column "F" to show true of false when i enter the time range.

the question is how can i stop the shapes being shown if it is not within the time.

at the moment it if the time is after the range it will not show which is good. but if the start / end time has expired it still shows.

any further help would be great