PDA

View Full Version : Colour cells using VBA



wisemang
03-27-2012, 03:43 AM
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
03-27-2012, 05:21 AM
hi

is this?

wisemang
03-27-2012, 05:29 AM
Hi Smartman

What do you mean by

"is This "?

wisemang
03-27-2012, 05:53 AM
Hi Smartman

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

Kenneth Hobs
03-27-2012, 06:17 AM
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
03-27-2012, 07:13 AM
Hi Smartman

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

download this attachmet

wisemang
03-27-2012, 07:23 AM
Excellent

Thank you Smartman

smartman
03-27-2012, 07:24 AM
Kenneth Hobs
thank you for fips