changing font color when clicking on cell

[quin]

I am looking for a way to make a faux button I suppose you could call it.
I would like to have 5 columns consisting of webding fonts that for instance, when clicked will change from gray to red.
I have 5 columns by 1500 rows so there are quite a few and they must all opperate independantly
I've tried labels but the file size gets very large and I'm not sure about how to loop for all 7500 labels

Does Excel recognize mouse events?
I've seen how this can be done by moving to a cell(buy means of mouse or keyboard) but that will break this spreadsheet [/code]

 

[erik.van.geit]

Hi, quin
Welcome to the Board !!!!!


seems like you're asking for a sheetevent (perhaps double click would be nice for you)
but at the end of your message it's like you're excluding to use such code

a bit more info would be great
"from gray to red"
so only grey cells will be changed to red ... nothing more ?
(f.i. red to grey again ? ...)

kind regards,
Erik

EDIT: still not sure... perhaps take a look at conditional format

 

[quin]

Hi erik!

I hope this helps!!

Vault Inventory Sheet(template).xls
	I	J	K	L	M
2
3	ORIGINALMEDIA
4	HARDCOPY	FILM(16or35mm)	MICROFICHE	APERTURECARD	DIGITAL
5	help
6			0		:
7			0		:
Vault Inventory

I would like to click in I:6 and have the font color change.
I would like it highlighted to show that the have a "hardcopy"

any means possible will work for me!!
thanks!

sorry about the fonts but they are webdings I:6 is a book, I:7 is CD and etc...
would a label work well for this also?
I know how to creat 1 label to click through 2 images but i don't know for 7500

 

[erik.van.geit]

still not sure ...
you didn't provide all necessary feedback to my questions, else I'm missing something

taking literally your statement

I would like to click in I:6 and have the font color change.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("I6:M" & Rows.Count), Me.UsedRange) Is Nothing Then Exit Sub

Target.Font.ColorIndex = 3

End Sub

http://www.cpearson.com/excel/events.htm
TO INSTALL IN SHEET CODE WINDOW:
1. right click the "Name Tab" of sheet you want code to work in
2. Select "View Code" in drop down menu
3. VBE window will open ... paste code in and exit VBE

also tell us what the "colorfont-change"-range would be
perhaps columns I to M starting at row 6 (I've made the code that way)

best regards,
Erik

 

[quin]

alright, sorry to be away for a bit...
I thought about what you were talking about erik and I came up hopefully with a better explination of what i need

the sheet event doubleclick sounds like it would work wonders for me!

How would one go about starting this?
lets say I have a fontcolor of grey in cell E6.I then double click the cell and the font color turns to red. double click the cell again and it turns back to grey.

If this does not work I have a labelwitht he following code:

Private Sub Label1_Click()
If Label1.ForeColor = &H80& Then
Label1.ForeColor = &HE0E0E0
Else
Label1.ForeColor = &H80&
End If
End Sub

how can i make this work for every label in the column without adding this code to every label?

Your example of Intersect did work but it gave to much freedom when using the keyboard to navigate cells. if only a mouse click did this it owuld be perfect.

I hope these examples help much more!

 

[erik.van.geit]

How would one go about starting this?
lets say I have a fontcolor of grey in cell E6.I then double click the cell and the font color turns to red. double click the cell again and it turns back to grey.

this is "toggle"code: red-grey-red-grey ...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("I6:M" & Rows.Count), Me.UsedRange) Is Nothing Then Exit Sub

'on of both lines
Target.Font.ColorIndex = IIf(Target.Font.ColorIndex = 3, 56, 3) 'instead of 56: 48,16,15
'Target.Font.Color = IIf(Target.Font.Color = &H80&, &HE0E0E0, &H80&)
Cancel = True
End Sub

this was not clear to me, but perhaps will be not relevant anymore

Your example of Intersect did work but it gave to much freedom when using the keyboard to navigate cells. if only a mouse click did this it owuld be perfect.

best regards,
Erik

 

[quin]

that worked great! thank you!!
what if i wanted column I to be red and J to be blue and so on?

 

[erik.van.geit]

and every column alternating with grey ?

 

[quin]

yes, grey is default color of text I have five columns Range("K7:O" & Rows.Count) K=Red, L=Gold, M=Orange, N=Black and O=Green

I'm sorry I didn't specify this earlier, I had no idea it would be so difficult to change it from one column acting independently to 5 columns acting independently

once again, thanks!

 

[erik.van.geit]

it's not so difficult, once your have your basic code working

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim col As Integer

If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("K6:O" & Rows.Count), Me.UsedRange) Is Nothing Then Exit Sub

    Select Case Target.Column
    Case 11: col = 3    'this is red, find out the other colors yourself, recording a macro
    Case 12: col = 4    'change numbers from here to get correct colors
    Case 13: col = 5
    Case 14: col = 6
    Case 15: col = 7
    End Select

Target.Font.ColorIndex = IIf(Target.Font.ColorIndex = col, 56, col) 'instead of 56: 48,16,15
Cancel = True
End Sub