Colour cells using VBA

[wisemang]

Hi

I am trying to complete the below and would like some help/guidance please using VBA.

EG.

Personal Rating

1 star 2 star 3 star 4 star 5 star

Above is an example of my excel spreadsheet. What i am trying to do is when i double click on 5 star for example it colours in 1-4 star also.And if i only double click in 3 star it only colours in 3,2 and 1

Please help:banghead:

[smartman]

hi

is this?

[wisemang]

Hi Smartman

What do you mean by

"is This "?

[wisemang]

Hi Smartman

Welcome.............I like the answer but how do i get it in the attached

[Kenneth Hobs]

Smartman, that is a fine example for shapes. Below is a modification of your code.

Wisemang, had you posted your workbook first, it would have been easier to see what you needed. There are two approaches to solve that. (1) Conditional Formatting and (2) Worksheet SelectionChange event. I would probably do (2) since this is a VBA forum. Method 2 is shown at the end.

Sub Stars(i As Integer)
Dim j As Integer, s As Shape
For j = 1 To 5
Set s = ActiveSheet.Shapes("Star" & j)
With s.Fill
.Visible = msoTrue
.ForeColor.Brightness = 0
.Transparency = 0
.Solid
If j <= i Then
.ForeColor.RGB = RGB(255, 255, 0)
Else
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.ForeColor.TintAndShade = 0
End If
End With
Next j
End Sub

Sub Star1()
Stars 1
End Sub
Sub Star2()
Stars 2
End Sub

Sub Star3()
Stars 3
End Sub

Sub Star4()
Stars 4
End Sub

Sub Star5()
Stars 5
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Dim i As Integer, r As Long
' This subroutine colors a cell red when double-clicked then clears it when double-clicked again.
' Some values for .ColorIndex are...
' Red = 3, Green = 4, Blue = 5, Yellow = 6, Orange = 45
' Google "VBA color palette" for more colors

With Target
r = .Row
If r = 1 Then Exit Sub
If .Column < 5 Or .Column > 9 Then Exit Sub

Range("E" & r, "I" & r).Interior.ColorIndex = xlAutomatic
Range("E" & r, Cells(r, .Column)).Interior.ColorIndex = 6

' This is to prevent the cell from being edited when double-clicked
Cancel = True
End With
End Sub

[smartman]

Hi Smartman

Welcome.............I like the answer but how do i get it in the attached

download this attachmet

[wisemang]

Excellent

Thank you Smartman

[smartman]

Kenneth Hobs
thank you for fips