Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > conditonal formatting in VB
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JBW JBW is offline
external usenet poster
  
Posts: 42
Default conditonal formatting in VB
All,

i'e got some VB that is working beautifully putting in a formula on a sheet
it then goes on to create various other sheets. The problem is I'm pretty new
to VB have recorded little macro to do conditional formatting of cells and
used (i thought) same rules, but always get compile error when I insert C
formatting instructions after formula one.

here is bit that works

ActiveSheet.Name = "BB SCM " & FormatDateTime(Date, vbLongDate)



myval = Application.CountIf(ActiveSheet.Cells, "NPQ") - 1


Range("Q6").Select
ActiveCell.FormulaR1C1 = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"
Range("Q6").Select
Selection.FormulaArray = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"


Dim counter As Integer

For counter = 1 To myval


Cells(6 + (counter * 8), 17).Select
ActiveCell.FormulaR1C1 = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"
Cells(6 + (counter * 8), 17).Select
Selection.FormulaArray = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"



Range("A6").Select
ActiveCell.FormulaR1C1 = "Stk Wk"
Range("B6").Select
ActiveCell.FormulaR1C1 = "=IF(RC[15]0,R[-1]C[2]/RC[15],""NF"")"
Range("B6").Select
Selection.NumberFormat = "0.0"


Cells(6 + (counter * 8), 1).Select
ActiveCell.FormulaR1C1 = "Stk Wk"
Cells(6 + (counter * 8), 2).Select
ActiveCell.FormulaR1C1 = "=IF(RC[15]0,R[-1]C[2]/RC[15],""NF"")"
Cells(6 + (counter * 8), 2).Select
Selection.NumberFormat = "0.0"

Range("Q1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Fc ave"



Next counter


here is bit for one cell that I wish to copy down to all releveant cells as
above

conditionalformatting Macro
' Macro recorded 12/10/2007 by jaberesf
'

'
Range("B6").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="2"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
Range("B7").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=""1.2*D6""", Formula2:="=""0.8*D6"""
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1,081
Default conditonal formatting in VB
Since you are just learning VBA, then one leson to learn RIGHT NOW is that
there is rarely a need to SELECT a cell or a range. So, your code

Range("Q6").Select
ActiveCell.FormulaR1C1 = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"
Range("Q6").Select
Selection.FormulaArray = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"

could be simplified to

Range("Q6").FormulaArray = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"

Similarly, this section of code

Cells(6 + (counter * 8), 17).Select
ActiveCell.FormulaR1C1 = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"
Cells(6 + (counter * 8), 17).Select
Selection.FormulaArray = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"

Range("A6").Select
ActiveCell.FormulaR1C1 = "Stk Wk"
Range("B6").Select
ActiveCell.FormulaR1C1 = "=IF(RC[15]0,R[-1]C[2]/RC[15],""NF"")"
Range("B6").Select
Selection.NumberFormat = "0.0"


can be shortened to

Cells(6 + (counter * 8), 17).FormulaArray = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"

Range("A6") = "Stk Wk"
With Range("B6")
.FormulaR1C1 = "=IF(RC[15]0,R[-1]C[2]/RC[15],""NF"")"
.NumberFormat = "0.0"
End With

and so on.


"JBW" wrote:

All,

i'e got some VB that is working beautifully putting in a formula on a sheet
it then goes on to create various other sheets. The problem is I'm pretty new
to VB have recorded little macro to do conditional formatting of cells and
used (i thought) same rules, but always get compile error when I insert C
formatting instructions after formula one.

here is bit that works

ActiveSheet.Name = "BB SCM " & FormatDateTime(Date, vbLongDate)



myval = Application.CountIf(ActiveSheet.Cells, "NPQ") - 1


Range("Q6").Select
ActiveCell.FormulaR1C1 = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"
Range("Q6").Select
Selection.FormulaArray = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"


Dim counter As Integer

For counter = 1 To myval


Cells(6 + (counter * 8), 17).Select
ActiveCell.FormulaR1C1 = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"
Cells(6 + (counter * 8), 17).Select
Selection.FormulaArray = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"



Range("A6").Select
ActiveCell.FormulaR1C1 = "Stk Wk"
Range("B6").Select
ActiveCell.FormulaR1C1 = "=IF(RC[15]0,R[-1]C[2]/RC[15],""NF"")"
Range("B6").Select
Selection.NumberFormat = "0.0"


Cells(6 + (counter * 8), 1).Select
ActiveCell.FormulaR1C1 = "Stk Wk"
Cells(6 + (counter * 8), 2).Select
ActiveCell.FormulaR1C1 = "=IF(RC[15]0,R[-1]C[2]/RC[15],""NF"")"
Cells(6 + (counter * 8), 2).Select
Selection.NumberFormat = "0.0"

Range("Q1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Fc ave"



Next counter


here is bit for one cell that I wish to copy down to all releveant cells as
above

conditionalformatting Macro
' Macro recorded 12/10/2007 by jaberesf
'

'
Range("B6").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="2"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
Range("B7").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=""1.2*D6""", Formula2:="=""0.8*D6"""
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1,081
Default conditonal formatting in VB
as far as your conditional formatting question goes, try something along
these lines

Dim r As Range
Set r = Range("b7")
With r
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlBetween, _
Formula1:="=1.2*" & .Offset(-1, 2).Address, _
Formula2:="=0.8*" & .Offset(-1, 2).Address
with .FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 3
END with
end with

"JBW" wrote:

All,

i'e got some VB that is working beautifully putting in a formula on a sheet
it then goes on to create various other sheets. The problem is I'm pretty new
to VB have recorded little macro to do conditional formatting of cells and
used (i thought) same rules, but always get compile error when I insert C
formatting instructions after formula one.

here is bit that works

ActiveSheet.Name = "BB SCM " & FormatDateTime(Date, vbLongDate)



myval = Application.CountIf(ActiveSheet.Cells, "NPQ") - 1


Range("Q6").Select
ActiveCell.FormulaR1C1 = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"
Range("Q6").Select
Selection.FormulaArray = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"


Dim counter As Integer

For counter = 1 To myval


Cells(6 + (counter * 8), 17).Select
ActiveCell.FormulaR1C1 = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"
Cells(6 + (counter * 8), 17).Select
Selection.FormulaArray = _
"=AVERAGE(IF(RC[-13]:RC[-2]0,RC[-13]:RC[-2],RC[-13]))"



Range("A6").Select
ActiveCell.FormulaR1C1 = "Stk Wk"
Range("B6").Select
ActiveCell.FormulaR1C1 = "=IF(RC[15]0,R[-1]C[2]/RC[15],""NF"")"
Range("B6").Select
Selection.NumberFormat = "0.0"


Cells(6 + (counter * 8), 1).Select
ActiveCell.FormulaR1C1 = "Stk Wk"
Cells(6 + (counter * 8), 2).Select
ActiveCell.FormulaR1C1 = "=IF(RC[15]0,R[-1]C[2]/RC[15],""NF"")"
Cells(6 + (counter * 8), 2).Select
Selection.NumberFormat = "0.0"

Range("Q1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Fc ave"



Next counter


here is bit for one cell that I wish to copy down to all releveant cells as
above

conditionalformatting Macro
' Macro recorded 12/10/2007 by jaberesf
'

'
Range("B6").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="2"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
Range("B7").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=""1.2*D6""", Formula2:="=""0.8*D6"""
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditonal formatting Hawksby Excel Discussion (Misc queries) 2 March 9th 07 02:49 PM
Self Tests, and Conditonal Formatting KiowaPilot Excel Discussion (Misc queries) 0 January 4th 07 07:49 PM
How do I do Conditonal Formatting with formula progression Steve Westwood Excel Worksheet Functions 4 August 24th 06 12:51 PM
Conditonal Formatting Matt Excel Discussion (Misc queries) 3 April 21st 06 08:42 PM
Protect conditonal formatting ITGuy Excel Discussion (Misc queries) 0 March 15th 05 11:47 AM


All times are GMT +1. The time now is 07:51 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement