changing font color when clicking on cell

quin

New Member
Joined
Sep 29, 2006
Messages
9
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]
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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 :confused:

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

New Member
Joined
Sep 29, 2006
Messages
9
Hi erik!

I hope this helps!!
Vault Inventory Sheet(template).xls
IJKLM
2
3ORIGINALMEDIA
4HARDCOPYFILM(16or35mm)MICROFICHEAPERTURECARDDIGITAL
5help
60:
70:
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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.
Code:
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

New Member
Joined
Sep 29, 2006
Messages
9

ADVERTISEMENT

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:
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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 ...
Code:
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

New Member
Joined
Sep 29, 2006
Messages
9

ADVERTISEMENT

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

quin

New Member
Joined
Sep 29, 2006
Messages
9
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
it's not so difficult, once your have your basic code working :)
Code:
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
 

Forum statistics

Threads
1,136,272
Messages
5,674,753
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top