| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting More Than three
What is the code to change the font to bold and also the color In a range in
Column D for five or six different conditions? For example: West change to RED East Change to BLUE North Change to Green Central Change to Yellow Thanks in advance. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting More Than three
Merry Xmas from Texas You could use a worksheet_change event in the sheet module with select case -- Don Guillett Microsoft MVP Excel SalesAid Software "Bob" wrote in message ... What is the code to change the font to bold and also the color In a range in Column D for five or six different conditions? For example: West change to RED East Change to BLUE North Change to Green Central Change to Yellow Thanks in advance. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting More Than three
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1:A10")) Is Nothing Then With Target Select Case .Value Case Is = "West" ..Font.Bold = True ..Interior.ColorIndex = 3 Case Is = "East" ..Font.Bold = True ..Interior.ColorIndex = 5 Case Is = "North" ..Font.Bold = True ..Interior.ColorIndex = 4 Case Is = "Central" ..Font.Bold = True ..Interior.ColorIndex = 6 Case Else ..Font.Bold = False ..Interior.ColorIndex = 0 End Select End With End If End Sub -- mikeaj72 ------------------------------------------------------------------------ mikeaj72's Profile: http://www.thecodecage.com/forumz/member.php?userid=46 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=44176 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting More Than three
Can you give me the complete code that should be added?
"Don Guillett" wrote: Merry Xmas from Texas You could use a worksheet_change event in the sheet module with select case -- Don Guillett Microsoft MVP Excel SalesAid Software "Bob" wrote in message ... What is the code to change the font to bold and also the color In a range in Column D for five or six different conditions? For example: West change to RED East Change to BLUE North Change to Green Central Change to Yellow Thanks in advance. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting More Than three
In case you can't figure this out. Right click sheet tabview
codecopy/paste thischange color numbers to suit Private Sub Worksheet_Change(ByVal Target As Range) Select Case LCase(Target) Case "west": mc = 4 Case "east": mc = 5 Case "north": mc = 6 Case "central": mc = 7 Case Else End Select With Target .Interior.ColorIndex = mc .Font.Bold = True End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Merry Xmas from Texas You could use a worksheet_change event in the sheet module with select case -- Don Guillett Microsoft MVP Excel SalesAid Software "Bob" wrote in message ... What is the code to change the font to bold and also the color In a range in Column D for five or six different conditions? For example: West change to RED East Change to BLUE North Change to Green Central Change to Yellow Thanks in advance. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting More Than three
Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range Dim iColor As Long Set R = Range("D1:D100") 'adjust to suit If Intersect(Target, R) Is Nothing Or _ Target.Count 1 Then Exit Sub Vals = Array("WEST", "EAST", "NORTH", "SOUTH") Nums = Array(3, 5, 10, 6) For i = LBound(Vals) To UBound(Vals) With Target If UCase(.Value) = Vals(i) Then iColor = Nums(i) If UCase(.Value) = Vals(i) Then .Font.Bold = True End With Next With Target .Interior.ColorIndex = iColor End With End Sub Pasted into the sheet module. Gord Dibben MS Excel MVP On Thu, 25 Dec 2008 08:29:00 -0800, Bob wrote: What is the code to change the font to bold and also the color In a range in Column D for five or six different conditions? For example: West change to RED East Change to BLUE North Change to Green Central Change to Yellow Thanks in advance. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting More Than three
Just a caveat with Don's code.
Cell font will turn Bold even if cell does not meet case select criteria. Don't know if that matters or not. Gord On Thu, 25 Dec 2008 11:10:04 -0600, "Don Guillett" wrote: In case you can't figure this out. Right click sheet tabview codecopy/paste thischange color numbers to suit Private Sub Worksheet_Change(ByVal Target As Range) Select Case LCase(Target) Case "west": mc = 4 Case "east": mc = 5 Case "north": mc = 6 Case "central": mc = 7 Case Else End Select With Target .Interior.ColorIndex = mc .Font.Bold = True End With End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting More Than three
The code works great if I retype the name in the cell. The sheet is already
filled out, is there a way to auto update with out retyping every name in the column over? "mikeaj72" wrote: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1:A10")) Is Nothing Then With Target Select Case .Value Case Is = "West" .Font.Bold = True .Interior.ColorIndex = 3 Case Is = "East" .Font.Bold = True .Interior.ColorIndex = 5 Case Is = "North" .Font.Bold = True .Interior.ColorIndex = 4 Case Is = "Central" .Font.Bold = True .Interior.ColorIndex = 6 Case Else .Font.Bold = False .Interior.ColorIndex = 0 End Select End With End If End Sub -- mikeaj72 ------------------------------------------------------------------------ mikeaj72's Profile: http://www.thecodecage.com/forumz/member.php?userid=46 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=44176 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting More Than three
Good point
With Target .Interior.ColorIndex = mc if target.interior.colorindex0 then .Font.Bold = True End With -- Don Guillett Microsoft MVP Excel SalesAid Software "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Just a caveat with Don's code. Cell font will turn Bold even if cell does not meet case select criteria. Don't know if that matters or not. Gord On Thu, 25 Dec 2008 11:10:04 -0600, "Don Guillett" wrote: In case you can't figure this out. Right click sheet tabview codecopy/paste thischange color numbers to suit Private Sub Worksheet_Change(ByVal Target As Range) Select Case LCase(Target) Case "west": mc = 4 Case "east": mc = 5 Case "north": mc = 6 Case "central": mc = 7 Case Else End Select With Target .Interior.ColorIndex = mc .Font.Bold = True End With End Sub |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting More Than three
Option Compare Text
Sub colorit() For Each cell In ActiveSheet.UsedRange With cell Select Case .Value Case Is = "West" ..Font.Bold = True ..Interior.ColorIndex = 3 Case Is = "East" ..Font.Bold = True ..Interior.ColorIndex = 5 Case Is = "North" ..Font.Bold = True ..Interior.ColorIndex = 4 Case Is = "Central" ..Font.Bold = True ..Interior.ColorIndex = 6 Case Else ..Font.Bold = False ..Interior.ColorIndex = 0 End Select End With Next End Sub Gord On Thu, 25 Dec 2008 09:40:01 -0800, Bob wrote: The code works great if I retype the name in the cell. The sheet is already filled out, is there a way to auto update with out retyping every name in the column over? "mikeaj72" wrote: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1:A10")) Is Nothing Then With Target Select Case .Value Case Is = "West" .Font.Bold = True .Interior.ColorIndex = 3 Case Is = "East" .Font.Bold = True .Interior.ColorIndex = 5 Case Is = "North" .Font.Bold = True .Interior.ColorIndex = 4 Case Is = "Central" .Font.Bold = True .Interior.ColorIndex = 6 Case Else .Font.Bold = False .Interior.ColorIndex = 0 End Select End With End If End Sub -- mikeaj72 ------------------------------------------------------------------------ mikeaj72's Profile: http://www.thecodecage.com/forumz/member.php?userid=46 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=44176 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I convert conditional formatting into explicit formatting? | Excel Discussion (Misc queries) | |||
| Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
| Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
| conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
| Conditional Formatting that will display conditional data | Excel Worksheet Functions | |||
Linear Mode
