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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.