Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colour Macro
Hi ,
I apologise in advance for the rudimentary nature of this post but I am new to Macros. What I am trying to do is create a macro that changes the colour of a word's font after it has been typed into a cell. For example when I type the word red into a cell I would like to be able to run a macro to turn the entry into the word red (typed in the red font colour). An associated question. Is it possible to have more than three types of conditional formatting within a single spreadsheet? thanks, Nik --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colour Macro
Hi
conditional format only accepts 3 conditions though you have a fourth if you include the default format. If you only want to apply different FONT colors based on NUMBERS, you can define up to 6 different styles. See: http://www.mcgimpsey.com/excel/conditional6.html for instructions how to do it For everything else you'll need VBA code (e.g. process the worksheet_change event and apply your format based on the cell values). The following will color the text in A1 red if the value in A1 is equal to 'red_word': Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub On Error GoTo CleanUp: With Target If .Value "red_word" Then .font.colorindex = 3 End If End With CleanUp: Application.EnableEvents = True End Sub HTH Frank Hi , I apologise in advance for the rudimentary nature of this post but I am new to Macros. What I am trying to do is create a macro that changes the colour of a word's font after it has been typed into a cell. For example when I type the word red into a cell I would like to be able to run a macro to turn the entry into the word red (typed in the red font colour). An associated question. Is it possible to have more than three types of conditional formatting within a single spreadsheet? thanks, Nik --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colour Macro
Nik,
You need event code like so Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ws_exit If Not Intersect(Target, Range("A1:A1000")) Is Nothing Then With Target Select Case LCase(.Value) Case "red": .Font.ColorIndex = 3 Case "green": .Font.ColorIndex = 10 'etc. End Select End With End If ws_exit: Application.EnableEvents = True End Sub which goes in the worksheet code module. Change the range to suit, and add extra cnditions. CF has four colurs in practice, as you have the 'no condition' colour. If you want more than this you need VBA again. This is an example I posted receently Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ws_exit With Target If .Count = 1 Then If .Column = 1 Then Select Case .Value Case Is = 1 .Interior.ColorIndex = 3 'red Case Is = 2 .Interior.ColorIndex = 38 'pink Case Is = 3 .Interior.ColorIndex = 4 'green Case Is = 4 .Interior.ColorIndex = 6 'yellow Case Is = 5 .Interior.ColorIndex = 8 'majenta Case Is = 6 .Interior.ColorIndex = 5 'blue Case Is = 7 .Interior.ColorIndex = 15 'grey Case Is = 8 .Interior.ColorIndex = 38 'rose Case Is = 9 .Interior.ColorIndex = 1 'teal Case Else 'none of the above numbers Exit Sub End Select End If End If End With ws_exit: Application.EnableEvents = True End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Ntisch " wrote in message ... Hi , I apologise in advance for the rudimentary nature of this post but I am new to Macros. What I am trying to do is create a macro that changes the colour of a word's font after it has been typed into a cell. For example when I type the word red into a cell I would like to be able to run a macro to turn the entry into the word red (typed in the red font colour). An associated question. Is it possible to have more than three types of conditional formatting within a single spreadsheet? thanks, Nik --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro for Tab colour change | Excel Discussion (Misc queries) | |||
macro help to change row colour automatically | Excel Worksheet Functions | |||
Colour macro | Excel Discussion (Misc queries) | |||
Change Tab colour using Macro | Excel Worksheet Functions | |||
Macro button colour change??? | Excel Worksheet Functions |