Apply formatting through code
Hi and TIA. I have a worksheet like so. I'm trying to conditionally format
the cells in Column C. If this is possible where do a place a call to the procedure? I want the procedure to run for each individual row except I can't simply copy the formula down the sheet because I have headers and totals rows. I'm new to excel. I'm an Access geek. Any advice or if you can point me in the right direction is appreciated. Thanks for your time! Header: USS Vinson A B C 1 3 3 1 2 2 3 4 3 1 2 2 Tot 6 8 7 Header: USS Eisenhower A B C 1 3 3 1 2 2 3 4 3 1 2 2 Tot 6 8 7 Header: Marine Detachment A B C 1 3 3 1 2 2 3 4 3 1 2 2 Tot 6 8 7 Select Case [A1] Case = 3 If [C1] = [B1], background is Green with White fonts If [C1] between [B1] - .01] and [B1] -.99], background is Yellow with Black Fonts If [C1] is less than [B1]- 1], background is Red with White fonts Case = 2 If [C1] = 2, background is Green with White fonts If [C1] = 1 or [C1] = 0, background is Red with White fonts Case = 1 If [C1] = 1, background is Green with White fonts If [C1] = 0, background is Red with White fonts End select -- Reggie -- Reggie |
Apply formatting through code
Are those really numbers in column A?
Are they constants or the results of numbers? If they're really numbers and constants, then try this: Select column A Edit|goto (or hit F5 or ctrl-g) click Special Constants|Numbers (uncheck the other stuff) and you should see just the rows to be inspected in the new selection. if that worked ok, you can do the same thing in code: Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Dim wks As Worksheet Set wks = Worksheets("Sheet3") With wks Set myRng = Nothing On Error Resume Next Set myRng _ = .Columns(1).Cells.SpecialCells(xlCellTypeConstants , xlNumbers) On Error GoTo 0 If myRng Is Nothing Then MsgBox "No numbers in column A" Exit Sub End If For Each myCell In myRng.Cells With myCell Select Case .Value Case Is = 3 If .Offset(0, 2).Value .Offset(0, 1).Value Then 'do formatting here ElseIf Abs(.Offset(0, 2).Value - .Offset(0, 1).Value) _ < 0.01 Then 'do formatting here ElseIf .Offset(0, 2).Value _ < .Offset(0, 1).Value - 1 Then 'do formatting here End If Case Is = 2 Select Case .Offset(0, 2).Value Case Is = 2 'do formatting here Case Is = 1, 0 'do formatting here End Select Case Is = 1 Select Case .Offset(0, 2).Value Case Is = 1 'do formatting here Case Is = 0 'do formatting here End Select End Select End With Next myCell End With End Sub If those numbers are the results of formulas, then you could use: ..Columns(1).Cells.SpecialCells(xlCellTypeConstant s, xlNumbers) instead of: ..Columns(1).Cells.SpecialCells(xlCellTypeFormulas , xlNumbers) And there are lots of shades of colors. I figured you could record a macro to get the colorindex numbers you want for each category. JRSmith wrote: Hi and TIA. I have a worksheet like so. I'm trying to conditionally format the cells in Column C. If this is possible where do a place a call to the procedure? I want the procedure to run for each individual row except I can't simply copy the formula down the sheet because I have headers and totals rows. I'm new to excel. I'm an Access geek. Any advice or if you can point me in the right direction is appreciated. Thanks for your time! Header: USS Vinson A B C 1 3 3 1 2 2 3 4 3 1 2 2 Tot 6 8 7 Header: USS Eisenhower A B C 1 3 3 1 2 2 3 4 3 1 2 2 Tot 6 8 7 Header: Marine Detachment A B C 1 3 3 1 2 2 3 4 3 1 2 2 Tot 6 8 7 Select Case [A1] Case = 3 If [C1] = [B1], background is Green with White fonts If [C1] between [B1] - .01] and [B1] -.99], background is Yellow with Black Fonts If [C1] is less than [B1]- 1], background is Red with White fonts Case = 2 If [C1] = 2, background is Green with White fonts If [C1] = 1 or [C1] = 0, background is Red with White fonts Case = 1 If [C1] = 1, background is Green with White fonts If [C1] = 0, background is Red with White fonts End select -- Reggie -- Reggie -- Dave Peterson |
Apply formatting through code
Dave, Thanks for the reply. Those are real numbers. I am pasting the
values directly from a recordset from MSAccess. I did the test you suggested and yes the only remaining selected values were the numbers. Have to go to my daughters play right now but will try the rest of your suggestion when I get back. Thanks much. Will let you know how it turns out. "Dave Peterson" wrote in message ... Are those really numbers in column A? Are they constants or the results of numbers? If they're really numbers and constants, then try this: Select column A Edit|goto (or hit F5 or ctrl-g) click Special Constants|Numbers (uncheck the other stuff) and you should see just the rows to be inspected in the new selection. if that worked ok, you can do the same thing in code: Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Dim wks As Worksheet Set wks = Worksheets("Sheet3") With wks Set myRng = Nothing On Error Resume Next Set myRng _ = .Columns(1).Cells.SpecialCells(xlCellTypeConstants , xlNumbers) On Error GoTo 0 If myRng Is Nothing Then MsgBox "No numbers in column A" Exit Sub End If For Each myCell In myRng.Cells With myCell Select Case .Value Case Is = 3 If .Offset(0, 2).Value .Offset(0, 1).Value Then 'do formatting here ElseIf Abs(.Offset(0, 2).Value - .Offset(0, 1).Value) _ < 0.01 Then 'do formatting here ElseIf .Offset(0, 2).Value _ < .Offset(0, 1).Value - 1 Then 'do formatting here End If Case Is = 2 Select Case .Offset(0, 2).Value Case Is = 2 'do formatting here Case Is = 1, 0 'do formatting here End Select Case Is = 1 Select Case .Offset(0, 2).Value Case Is = 1 'do formatting here Case Is = 0 'do formatting here End Select End Select End With Next myCell End With End Sub If those numbers are the results of formulas, then you could use: .Columns(1).Cells.SpecialCells(xlCellTypeConstants , xlNumbers) instead of: .Columns(1).Cells.SpecialCells(xlCellTypeFormulas, xlNumbers) And there are lots of shades of colors. I figured you could record a macro to get the colorindex numbers you want for each category. JRSmith wrote: Hi and TIA. I have a worksheet like so. I'm trying to conditionally format the cells in Column C. If this is possible where do a place a call to the procedure? I want the procedure to run for each individual row except I can't simply copy the formula down the sheet because I have headers and totals rows. I'm new to excel. I'm an Access geek. Any advice or if you can point me in the right direction is appreciated. Thanks for your time! Header: USS Vinson A B C 1 3 3 1 2 2 3 4 3 1 2 2 Tot 6 8 7 Header: USS Eisenhower A B C 1 3 3 1 2 2 3 4 3 1 2 2 Tot 6 8 7 Header: Marine Detachment A B C 1 3 3 1 2 2 3 4 3 1 2 2 Tot 6 8 7 Select Case [A1] Case = 3 If [C1] = [B1], background is Green with White fonts If [C1] between [B1] - .01] and [B1] -.99], background is Yellow with Black Fonts If [C1] is less than [B1]- 1], background is Red with White fonts Case = 2 If [C1] = 2, background is Green with White fonts If [C1] = 1 or [C1] = 0, background is Red with White fonts Case = 1 If [C1] = 1, background is Green with White fonts If [C1] = 0, background is Red with White fonts End select -- Reggie -- Reggie -- Dave Peterson -- Reggie |
Apply formatting through code
Dave, Your the man. Worked like a charm. Can't thank you enough. I'm an
access geek so this is a little new. This is what I ended up with. Basicly just added some variables and did my logic on them. Refrencing all them cells and offsets blah confuses me which aint to hard to do. Getting the hang of it though. Take care. Sub RBA_Cond_Form() Dim myCell As Range Dim myRng As Range Dim wks As Worksheet Set wks = Worksheets("Sheet1") With wks Set myRng = Nothing On Error Resume Next Set myRng _ = .Columns(10).Cells.SpecialCells(xlCellTypeConstant s, xlNumbers) On Error GoTo 0 If myRng Is Nothing Then MsgBox "No numbers in column A" Exit Sub End If For Each myCell In myRng.Cells With myCell Dim lngRBASTD As Single Dim lngRBA As Single Dim lngRBAFCF As Single Dim lngRBASTDUpper As Single Dim lngRBASTDLower As Single lngRBASTD = .Offset(0, 1).Value lngRBA = .Offset(0, 2).Value lngRBAFCF = .Offset(0, 4).Value lngRBASTDUpper = Abs((.Offset(0, 1).Value) - 0.01) lngRBASTDLower = Abs((.Offset(0, 1).Value) - 0.99) Select Case .Value 'FL Value Case Is = 3 If lngRBA = lngRBASTD Then .Offset(0, 2).Interior.ColorIndex = 10 .Offset(0, 2).Font.ColorIndex = 2 ElseIf (lngRBA = lngRBASTDLower) And (lngRBA <= lngRBASTDUpper) Then .Offset(0, 2).Interior.ColorIndex = 6 .Offset(0, 2).Font.ColorIndex = 1 ElseIf lngRBA < (lngRBASTD - 1) Then .Offset(0, 2).Interior.ColorIndex = 3 .Offset(0, 2).Font.ColorIndex = 2 End If Case Is = 2 Select Case lngRBA Case Is = 2 .Offset(0, 2).Interior.ColorIndex = 10 .Offset(0, 2).Font.ColorIndex = 2 Case Is = 1, 0 .Offset(0, 2).Interior.ColorIndex = 3 .Offset(0, 2).Font.ColorIndex = 2 End Select Case Is = 1 Select Case lngRBA Case Is = 1 .Offset(0, 2).Interior.ColorIndex = 10 .Offset(0, 2).Font.ColorIndex = 2 Case Is = 0 .Offset(0, 2).Interior.ColorIndex = 3 .Offset(0, 2).Font.ColorIndex = 2 End Select End Select End With Next myCell End With End Sub "Dave Peterson" wrote in message ... Are those really numbers in column A? Are they constants or the results of numbers? If they're really numbers and constants, then try this: Select column A Edit|goto (or hit F5 or ctrl-g) click Special Constants|Numbers (uncheck the other stuff) and you should see just the rows to be inspected in the new selection. if that worked ok, you can do the same thing in code: Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Dim wks As Worksheet Set wks = Worksheets("Sheet3") With wks Set myRng = Nothing On Error Resume Next Set myRng _ = .Columns(1).Cells.SpecialCells(xlCellTypeConstants , xlNumbers) On Error GoTo 0 If myRng Is Nothing Then MsgBox "No numbers in column A" Exit Sub End If For Each myCell In myRng.Cells With myCell Select Case .Value Case Is = 3 If .Offset(0, 2).Value .Offset(0, 1).Value Then 'do formatting here ElseIf Abs(.Offset(0, 2).Value - .Offset(0, 1).Value) _ < 0.01 Then 'do formatting here ElseIf .Offset(0, 2).Value _ < .Offset(0, 1).Value - 1 Then 'do formatting here End If Case Is = 2 Select Case .Offset(0, 2).Value Case Is = 2 'do formatting here Case Is = 1, 0 'do formatting here End Select Case Is = 1 Select Case .Offset(0, 2).Value Case Is = 1 'do formatting here Case Is = 0 'do formatting here End Select End Select End With Next myCell End With End Sub If those numbers are the results of formulas, then you could use: .Columns(1).Cells.SpecialCells(xlCellTypeConstants , xlNumbers) instead of: .Columns(1).Cells.SpecialCells(xlCellTypeFormulas, xlNumbers) And there are lots of shades of colors. I figured you could record a macro to get the colorindex numbers you want for each category. JRSmith wrote: Hi and TIA. I have a worksheet like so. I'm trying to conditionally format the cells in Column C. If this is possible where do a place a call to the procedure? I want the procedure to run for each individual row except I can't simply copy the formula down the sheet because I have headers and totals rows. I'm new to excel. I'm an Access geek. Any advice or if you can point me in the right direction is appreciated. Thanks for your time! Header: USS Vinson A B C 1 3 3 1 2 2 3 4 3 1 2 2 Tot 6 8 7 Header: USS Eisenhower A B C 1 3 3 1 2 2 3 4 3 1 2 2 Tot 6 8 7 Header: Marine Detachment A B C 1 3 3 1 2 2 3 4 3 1 2 2 Tot 6 8 7 Select Case [A1] Case = 3 If [C1] = [B1], background is Green with White fonts If [C1] between [B1] - .01] and [B1] -.99], background is Yellow with Black Fonts If [C1] is less than [B1]- 1], background is Red with White fonts Case = 2 If [C1] = 2, background is Green with White fonts If [C1] = 1 or [C1] = 0, background is Red with White fonts Case = 1 If [C1] = 1, background is Green with White fonts If [C1] = 0, background is Red with White fonts End select -- Reggie -- Reggie -- Dave Peterson -- Reggie |
Apply formatting through code
I'd move all those dim's to the top of the procedure -- no reason to declare
them over and over in your loop. And I'd use "As Double". It turns out that Doubles are more efficient than Singles and that's how excel would retrieve the values from the worksheet anyway. JRSmith wrote: Dave, Your the man. Worked like a charm. Can't thank you enough. I'm an access geek so this is a little new. This is what I ended up with. Basicly just added some variables and did my logic on them. Refrencing all them cells and offsets blah confuses me which aint to hard to do. Getting the hang of it though. Take care. Sub RBA_Cond_Form() Dim myCell As Range Dim myRng As Range Dim wks As Worksheet Set wks = Worksheets("Sheet1") With wks Set myRng = Nothing On Error Resume Next Set myRng _ = .Columns(10).Cells.SpecialCells(xlCellTypeConstant s, xlNumbers) On Error GoTo 0 If myRng Is Nothing Then MsgBox "No numbers in column A" Exit Sub End If For Each myCell In myRng.Cells With myCell Dim lngRBASTD As Single Dim lngRBA As Single Dim lngRBAFCF As Single Dim lngRBASTDUpper As Single Dim lngRBASTDLower As Single lngRBASTD = .Offset(0, 1).Value lngRBA = .Offset(0, 2).Value lngRBAFCF = .Offset(0, 4).Value lngRBASTDUpper = Abs((.Offset(0, 1).Value) - 0.01) lngRBASTDLower = Abs((.Offset(0, 1).Value) - 0.99) Select Case .Value 'FL Value Case Is = 3 If lngRBA = lngRBASTD Then .Offset(0, 2).Interior.ColorIndex = 10 .Offset(0, 2).Font.ColorIndex = 2 ElseIf (lngRBA = lngRBASTDLower) And (lngRBA <= lngRBASTDUpper) Then .Offset(0, 2).Interior.ColorIndex = 6 .Offset(0, 2).Font.ColorIndex = 1 ElseIf lngRBA < (lngRBASTD - 1) Then .Offset(0, 2).Interior.ColorIndex = 3 .Offset(0, 2).Font.ColorIndex = 2 End If Case Is = 2 Select Case lngRBA Case Is = 2 .Offset(0, 2).Interior.ColorIndex = 10 .Offset(0, 2).Font.ColorIndex = 2 Case Is = 1, 0 .Offset(0, 2).Interior.ColorIndex = 3 .Offset(0, 2).Font.ColorIndex = 2 End Select Case Is = 1 Select Case lngRBA Case Is = 1 .Offset(0, 2).Interior.ColorIndex = 10 .Offset(0, 2).Font.ColorIndex = 2 Case Is = 0 .Offset(0, 2).Interior.ColorIndex = 3 .Offset(0, 2).Font.ColorIndex = 2 End Select End Select End With Next myCell End With End Sub "Dave Peterson" wrote in message ... Are those really numbers in column A? Are they constants or the results of numbers? If they're really numbers and constants, then try this: Select column A Edit|goto (or hit F5 or ctrl-g) click Special Constants|Numbers (uncheck the other stuff) and you should see just the rows to be inspected in the new selection. if that worked ok, you can do the same thing in code: Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Dim wks As Worksheet Set wks = Worksheets("Sheet3") With wks Set myRng = Nothing On Error Resume Next Set myRng _ = .Columns(1).Cells.SpecialCells(xlCellTypeConstants , xlNumbers) On Error GoTo 0 If myRng Is Nothing Then MsgBox "No numbers in column A" Exit Sub End If For Each myCell In myRng.Cells With myCell Select Case .Value Case Is = 3 If .Offset(0, 2).Value .Offset(0, 1).Value Then 'do formatting here ElseIf Abs(.Offset(0, 2).Value - .Offset(0, 1).Value) _ < 0.01 Then 'do formatting here ElseIf .Offset(0, 2).Value _ < .Offset(0, 1).Value - 1 Then 'do formatting here End If Case Is = 2 Select Case .Offset(0, 2).Value Case Is = 2 'do formatting here Case Is = 1, 0 'do formatting here End Select Case Is = 1 Select Case .Offset(0, 2).Value Case Is = 1 'do formatting here Case Is = 0 'do formatting here End Select End Select End With Next myCell End With End Sub If those numbers are the results of formulas, then you could use: .Columns(1).Cells.SpecialCells(xlCellTypeConstants , xlNumbers) instead of: .Columns(1).Cells.SpecialCells(xlCellTypeFormulas, xlNumbers) And there are lots of shades of colors. I figured you could record a macro to get the colorindex numbers you want for each category. JRSmith wrote: Hi and TIA. I have a worksheet like so. I'm trying to conditionally format the cells in Column C. If this is possible where do a place a call to the procedure? I want the procedure to run for each individual row except I can't simply copy the formula down the sheet because I have headers and totals rows. I'm new to excel. I'm an Access geek. Any advice or if you can point me in the right direction is appreciated. Thanks for your time! Header: USS Vinson A B C 1 3 3 1 2 2 3 4 3 1 2 2 Tot 6 8 7 Header: USS Eisenhower A B C 1 3 3 1 2 2 3 4 3 1 2 2 Tot 6 8 7 Header: Marine Detachment A B C 1 3 3 1 2 2 3 4 3 1 2 2 Tot 6 8 7 Select Case [A1] Case = 3 If [C1] = [B1], background is Green with White fonts If [C1] between [B1] - .01] and [B1] -.99], background is Yellow with Black Fonts If [C1] is less than [B1]- 1], background is Red with White fonts Case = 2 If [C1] = 2, background is Green with White fonts If [C1] = 1 or [C1] = 0, background is Red with White fonts Case = 1 If [C1] = 1, background is Green with White fonts If [C1] = 0, background is Red with White fonts End select -- Reggie -- Reggie -- Dave Peterson -- Reggie -- Dave Peterson |
Apply formatting through code
I'll do it. Got a little carried away. Thanks again.
"Dave Peterson" wrote in message ... I'd move all those dim's to the top of the procedure -- no reason to declare them over and over in your loop. And I'd use "As Double". It turns out that Doubles are more efficient than Singles and that's how excel would retrieve the values from the worksheet anyway. JRSmith wrote: Dave, Your the man. Worked like a charm. Can't thank you enough. I'm an access geek so this is a little new. This is what I ended up with. Basicly just added some variables and did my logic on them. Refrencing all them cells and offsets blah confuses me which aint to hard to do. Getting the hang of it though. Take care. Sub RBA_Cond_Form() Dim myCell As Range Dim myRng As Range Dim wks As Worksheet Set wks = Worksheets("Sheet1") With wks Set myRng = Nothing On Error Resume Next Set myRng _ = .Columns(10).Cells.SpecialCells(xlCellTypeConstant s, xlNumbers) On Error GoTo 0 If myRng Is Nothing Then MsgBox "No numbers in column A" Exit Sub End If For Each myCell In myRng.Cells With myCell Dim lngRBASTD As Single Dim lngRBA As Single Dim lngRBAFCF As Single Dim lngRBASTDUpper As Single Dim lngRBASTDLower As Single lngRBASTD = .Offset(0, 1).Value lngRBA = .Offset(0, 2).Value lngRBAFCF = .Offset(0, 4).Value lngRBASTDUpper = Abs((.Offset(0, 1).Value) - 0.01) lngRBASTDLower = Abs((.Offset(0, 1).Value) - 0.99) Select Case .Value 'FL Value Case Is = 3 If lngRBA = lngRBASTD Then .Offset(0, 2).Interior.ColorIndex = 10 .Offset(0, 2).Font.ColorIndex = 2 ElseIf (lngRBA = lngRBASTDLower) And (lngRBA <= lngRBASTDUpper) Then .Offset(0, 2).Interior.ColorIndex = 6 .Offset(0, 2).Font.ColorIndex = 1 ElseIf lngRBA < (lngRBASTD - 1) Then .Offset(0, 2).Interior.ColorIndex = 3 .Offset(0, 2).Font.ColorIndex = 2 End If Case Is = 2 Select Case lngRBA Case Is = 2 .Offset(0, 2).Interior.ColorIndex = 10 .Offset(0, 2).Font.ColorIndex = 2 Case Is = 1, 0 .Offset(0, 2).Interior.ColorIndex = 3 .Offset(0, 2).Font.ColorIndex = 2 End Select Case Is = 1 Select Case lngRBA Case Is = 1 .Offset(0, 2).Interior.ColorIndex = 10 .Offset(0, 2).Font.ColorIndex = 2 Case Is = 0 .Offset(0, 2).Interior.ColorIndex = 3 .Offset(0, 2).Font.ColorIndex = 2 End Select End Select End With Next myCell End With End Sub "Dave Peterson" wrote in message ... Are those really numbers in column A? Are they constants or the results of numbers? If they're really numbers and constants, then try this: Select column A Edit|goto (or hit F5 or ctrl-g) click Special Constants|Numbers (uncheck the other stuff) and you should see just the rows to be inspected in the new selection. if that worked ok, you can do the same thing in code: Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Dim wks As Worksheet Set wks = Worksheets("Sheet3") With wks Set myRng = Nothing On Error Resume Next Set myRng _ = .Columns(1).Cells.SpecialCells(xlCellTypeConstants , xlNumbers) On Error GoTo 0 If myRng Is Nothing Then MsgBox "No numbers in column A" Exit Sub End If For Each myCell In myRng.Cells With myCell Select Case .Value Case Is = 3 If .Offset(0, 2).Value .Offset(0, 1).Value Then 'do formatting here ElseIf Abs(.Offset(0, 2).Value - .Offset(0, 1).Value) _ < 0.01 Then 'do formatting here ElseIf .Offset(0, 2).Value _ < .Offset(0, 1).Value - 1 Then 'do formatting here End If Case Is = 2 Select Case .Offset(0, 2).Value Case Is = 2 'do formatting here Case Is = 1, 0 'do formatting here End Select Case Is = 1 Select Case .Offset(0, 2).Value Case Is = 1 'do formatting here Case Is = 0 'do formatting here End Select End Select End With Next myCell End With End Sub If those numbers are the results of formulas, then you could use: .Columns(1).Cells.SpecialCells(xlCellTypeConstants , xlNumbers) instead of: .Columns(1).Cells.SpecialCells(xlCellTypeFormulas, xlNumbers) And there are lots of shades of colors. I figured you could record a macro to get the colorindex numbers you want for each category. JRSmith wrote: Hi and TIA. I have a worksheet like so. I'm trying to conditionally format the cells in Column C. If this is possible where do a place a call to the procedure? I want the procedure to run for each individual row except I can't simply copy the formula down the sheet because I have headers and totals rows. I'm new to excel. I'm an Access geek. Any advice or if you can point me in the right direction is appreciated. Thanks for your time! Header: USS Vinson A B C 1 3 3 1 2 2 3 4 3 1 2 2 Tot 6 8 7 Header: USS Eisenhower A B C 1 3 3 1 2 2 3 4 3 1 2 2 Tot 6 8 7 Header: Marine Detachment A B C 1 3 3 1 2 2 3 4 3 1 2 2 Tot 6 8 7 Select Case [A1] Case = 3 If [C1] = [B1], background is Green with White fonts If [C1] between [B1] - .01] and [B1] -.99], background is Yellow with Black Fonts If [C1] is less than [B1]- 1], background is Red with White fonts Case = 2 If [C1] = 2, background is Green with White fonts If [C1] = 1 or [C1] = 0, background is Red with White fonts Case = 1 If [C1] = 1, background is Green with White fonts If [C1] = 0, background is Red with White fonts End select -- Reggie -- Reggie -- Dave Peterson -- Reggie -- Dave Peterson -- Reggie |
All times are GMT +1. The time now is 09:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com