Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting of Cell
Hello, I have a worksheet whos data comes from SQL Server db. Autofilter is
turned on. Within the worksheet are many columns. I would like to change the backcolor of the cells in column C based on the content of the cell in column D (could be "PASS", "WATCH", or "FAIL") on the same row (i.e. c1=5 d1='Pass' change backcolor of c1 to Green, or c1=7 d1='WATCH' change backcolor to Yellow, or c1=10 d1='FAIL' change backcolor to Red). How do I identify the range and apply correct backcolor to cells within range? Need to be coded using vba macro? -- DC -- Dee |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting of Cell
one other thing, just in case there may not be a value in column D:
Select Case UCase(.Value) Case "FAIL" .Offset(, -1).Interior.ColorIndex = 3 Case "WATCH" .Offset(, -1).Interior.ColorIndex = 19 Case "PASS" .Offset(, -1).Interior.ColorIndex = 35 Case Else .Offset(, -1).Interior.ColorIndex = 0 End Select -- Gary "Gary Keramidas" <GKeramidasAtMsn.com wrote in message ... i don't always use conditional formatting unless i'm going to change data between runs. so, if you run your query and run code, something like this should work. Sub test() Dim i As Long Dim ws As Worksheet Dim lastrow As Long Set ws = Worksheets("Sheet1") lastrow = ws.Cells(Rows.Count, "C").End(xlUp).Row For i = 1 To lastrow With ws.Range("D" & i) Select Case UCase(.Value) Case "FAIL" .Offset(, -1).Interior.ColorIndex = 3 Case "WATCH" .Offset(, -1).Interior.ColorIndex = 19 Case "PASS" .Offset(, -1).Interior.ColorIndex = 35 End Select End With Next End Sub -- Gary "Dee" wrote in message ... Hello, I have a worksheet whos data comes from SQL Server db. Autofilter is turned on. Within the worksheet are many columns. I would like to change the backcolor of the cells in column C based on the content of the cell in column D (could be "PASS", "WATCH", or "FAIL") on the same row (i.e. c1=5 d1='Pass' change backcolor of c1 to Green, or c1=7 d1='WATCH' change backcolor to Yellow, or c1=10 d1='FAIL' change backcolor to Red). How do I identify the range and apply correct backcolor to cells within range? Need to be coded using vba macro? -- DC -- Dee |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting of Cell
Try this. It will set the conditional format of the cells in the range based
upon the value in the cell to the RIGHT. Dim myRange As Range Set myRange = Range("C1:C10") With myRange .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=RC[1]=""PASS""" .FormatConditions(1).Interior.ColorIndex = 4 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=RC[1]=""WATCH""" .FormatConditions(2).Interior.ColorIndex = 6 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=RC[1]=""FAIL""" .FormatConditions(3).Font.ColorIndex = 2 .FormatConditions(3).Interior.ColorIndex = 3 End With -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Dee" wrote: Hello, I have a worksheet whos data comes from SQL Server db. Autofilter is turned on. Within the worksheet are many columns. I would like to change the backcolor of the cells in column C based on the content of the cell in column D (could be "PASS", "WATCH", or "FAIL") on the same row (i.e. c1=5 d1='Pass' change backcolor of c1 to Green, or c1=7 d1='WATCH' change backcolor to Yellow, or c1=10 d1='FAIL' change backcolor to Red). How do I identify the range and apply correct backcolor to cells within range? Need to be coded using vba macro? -- DC -- Dee |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting of Cell
Barb, is there a way to code the range for the statement "Set myRange =
Range("C1:C10") " my db will update each month and add rows. Dont want to have to change code every month. Thanks, DC -- DC "Barb Reinhardt" wrote: Try this. It will set the conditional format of the cells in the range based upon the value in the cell to the RIGHT. Dim myRange As Range Set myRange = Range("C1:C10") With myRange .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=RC[1]=""PASS""" .FormatConditions(1).Interior.ColorIndex = 4 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=RC[1]=""WATCH""" .FormatConditions(2).Interior.ColorIndex = 6 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=RC[1]=""FAIL""" .FormatConditions(3).Font.ColorIndex = 2 .FormatConditions(3).Interior.ColorIndex = 3 End With -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Dee" wrote: Hello, I have a worksheet whos data comes from SQL Server db. Autofilter is turned on. Within the worksheet are many columns. I would like to change the backcolor of the cells in column C based on the content of the cell in column D (could be "PASS", "WATCH", or "FAIL") on the same row (i.e. c1=5 d1='Pass' change backcolor of c1 to Green, or c1=7 d1='WATCH' change backcolor to Yellow, or c1=10 d1='FAIL' change backcolor to Red). How do I identify the range and apply correct backcolor to cells within range? Need to be coded using vba macro? -- DC -- Dee |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting of Cell
hi
you will have to have some way of telling vb what range to apply the cf to. you could add an input box so that the code will ask you what range. Dim myRange As Range Dim r As String r = InputBox("enter range") Set myRange = Range(r) 'Range("C1:C10") With myRange .ect or if you don't want to be bothered by input, you could do this. Dim myRange As Range Dim r As Long r = Cells(Rows.Count, "C").End(xlUp).Row Set myRange = Range("C1:C" & r) with myrange .ect this way vb would just find the last row and use it in the range. regards FSt1 "Dee" wrote: Barb, is there a way to code the range for the statement "Set myRange = Range("C1:C10") " my db will update each month and add rows. Dont want to have to change code every month. Thanks, DC -- DC "Barb Reinhardt" wrote: Try this. It will set the conditional format of the cells in the range based upon the value in the cell to the RIGHT. Dim myRange As Range Set myRange = Range("C1:C10") With myRange .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=RC[1]=""PASS""" .FormatConditions(1).Interior.ColorIndex = 4 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=RC[1]=""WATCH""" .FormatConditions(2).Interior.ColorIndex = 6 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=RC[1]=""FAIL""" .FormatConditions(3).Font.ColorIndex = 2 .FormatConditions(3).Interior.ColorIndex = 3 End With -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Dee" wrote: Hello, I have a worksheet whos data comes from SQL Server db. Autofilter is turned on. Within the worksheet are many columns. I would like to change the backcolor of the cells in column C based on the content of the cell in column D (could be "PASS", "WATCH", or "FAIL") on the same row (i.e. c1=5 d1='Pass' change backcolor of c1 to Green, or c1=7 d1='WATCH' change backcolor to Yellow, or c1=10 d1='FAIL' change backcolor to Red). How do I identify the range and apply correct backcolor to cells within range? Need to be coded using vba macro? -- DC -- Dee |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting of Cell
Thanks All, for replying. All were very good solutions. I decided to use
Gary's suggestion in my program, it was the easiest to follow. Others will have to maintain code, it will take less time explaining to others. Thanks again, you all are save me a lot of time. -- DC "FSt1" wrote: hi you will have to have some way of telling vb what range to apply the cf to. you could add an input box so that the code will ask you what range. Dim myRange As Range Dim r As String r = InputBox("enter range") Set myRange = Range(r) 'Range("C1:C10") With myRange .ect or if you don't want to be bothered by input, you could do this. Dim myRange As Range Dim r As Long r = Cells(Rows.Count, "C").End(xlUp).Row Set myRange = Range("C1:C" & r) with myrange .ect this way vb would just find the last row and use it in the range. regards FSt1 "Dee" wrote: Barb, is there a way to code the range for the statement "Set myRange = Range("C1:C10") " my db will update each month and add rows. Dont want to have to change code every month. Thanks, DC -- DC "Barb Reinhardt" wrote: Try this. It will set the conditional format of the cells in the range based upon the value in the cell to the RIGHT. Dim myRange As Range Set myRange = Range("C1:C10") With myRange .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=RC[1]=""PASS""" .FormatConditions(1).Interior.ColorIndex = 4 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=RC[1]=""WATCH""" .FormatConditions(2).Interior.ColorIndex = 6 .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=RC[1]=""FAIL""" .FormatConditions(3).Font.ColorIndex = 2 .FormatConditions(3).Interior.ColorIndex = 3 End With -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Dee" wrote: Hello, I have a worksheet whos data comes from SQL Server db. Autofilter is turned on. Within the worksheet are many columns. I would like to change the backcolor of the cells in column C based on the content of the cell in column D (could be "PASS", "WATCH", or "FAIL") on the same row (i.e. c1=5 d1='Pass' change backcolor of c1 to Green, or c1=7 d1='WATCH' change backcolor to Yellow, or c1=10 d1='FAIL' change backcolor to Red). How do I identify the range and apply correct backcolor to cells within range? Need to be coded using vba macro? -- DC -- Dee |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting of Cell
i don't always use conditional formatting unless i'm going to change data
between runs. so, if you run your query and run code, something like this should work. Sub test() Dim i As Long Dim ws As Worksheet Dim lastrow As Long Set ws = Worksheets("Sheet1") lastrow = ws.Cells(Rows.Count, "C").End(xlUp).Row For i = 1 To lastrow With ws.Range("D" & i) Select Case UCase(.Value) Case "FAIL" .Offset(, -1).Interior.ColorIndex = 3 Case "WATCH" .Offset(, -1).Interior.ColorIndex = 19 Case "PASS" .Offset(, -1).Interior.ColorIndex = 35 End Select End With Next End Sub -- Gary "Dee" wrote in message ... Hello, I have a worksheet whos data comes from SQL Server db. Autofilter is turned on. Within the worksheet are many columns. I would like to change the backcolor of the cells in column C based on the content of the cell in column D (could be "PASS", "WATCH", or "FAIL") on the same row (i.e. c1=5 d1='Pass' change backcolor of c1 to Green, or c1=7 d1='WATCH' change backcolor to Yellow, or c1=10 d1='FAIL' change backcolor to Red). How do I identify the range and apply correct backcolor to cells within range? Need to be coded using vba macro? -- DC -- Dee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting--different formatting depending on cell con | Excel Discussion (Misc queries) | |||
Conditional formatting Based on cell A text with conditions in Cell B | 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 based on cell formatting | Excel Worksheet Functions |