score:3
formula1:="=$ci3=""ties material"""
"if column ci contains the text "ties material" then it should format the cell to the color white"
to do this, your format condition should be:
formula1:="=isnumber(search(""ties material"", $ci" & firstcell.row & "))"
as for the second condition i still dont get the idea you are trying to achieve. however the formula might be correct, but the problem is that you are referring to it wrongly:
with activeworkbook.sheets("material").range(firstcell, lastcell).formatconditions(1).interior
as this is the second formatcondition
, you should refer to it as index (2)
. this explains why you were actually overwriting the format of the first condition with red, while the second had no format set.
with activeworkbook.sheets("material").range(firstcell, lastcell).formatconditions(2).interior
' ^^^
(this supposes both your cfs apply on the same range). if not, a usually safe method is to get a reference on the cf directly and work with it:
with myrange.formatconditions.add(xlexpression, formula1)
.interior.colorindex = ...
. etc...
end with
score:2
ok. again it is a bit difficult to understand what you tried to achieve and what went wrong as you gave no test material (and the columns are that far to the right that i had to make it simpler...), but it may be just me.
to format the cell color to white when the column ci read ties material your rule formula "=$ci3=""ties material""" works fine but i just wonder what colour would they be when they are not white? would they be red due to the other rule? then they are in wrong order as conflicting rules will take the rule higher in the list to be applied. the 'stop if true' is for the pre 2007 versions of excel.
and one can see an error in the image and it comes from your code: in the manage rules dialog image you see that the 'white rule' is marked as 'no format set'. that is because you have referred to the formatconditions(1) in the both of the procedures. if you have first run the 'white rule' and the 'red' the latter has been set ok but simultaneously broken the first (number one) most probably as the references to the range don't match.
so maybe you want to run the 'white rule' first and refer to formatconditions(2) when creating the 'red', but as i said hard to tell. :-)
score:3
the formulas show up correctly but the colors are in the opposite sections that they need to be in.
that is not entirely correct. you create one cfr with a white background and set it to the top of the list. then you create a second but before putting it to the top of the list, you change the top of the list cfr to a red background. so you have one cfr that used to be a white background and is now red background and a second cfr with no background.
i'm going to assume that the formula for the red cfr is correct. someone else suggested a non-volatile change.
option explicit
private sub white_red_cfrs()
'this section adds the formatting condition of white cells to the cells that are changed by the pemcon
dim lrow as long, lcol as long, firstcell as string, lastcell as string
dim colcount as long, lastheadercell as long
with activeworkbook.worksheets("material")
lrow = .range("a2").end(xldown).row
lcol = .range("a2").end(xltoright).column
firstcell = .range("cu3").address(false, false)
lastcell = .cells(lrow, lcol).address(false, false)
with .range(firstcell, lastcell)
.formatconditions.delete
with .formatconditions.add(type:=xlexpression, formula1:="=$ci3=""ties material""")
.interior.color = 16777215 'this is the color white
.setfirstpriority
.stopiftrue = true
end with
with .formatconditions.add(type:=xlexpression, formula1:="=offset($a$1,row()-1,column()-1)<>offset($a$1,row()-1,column()+" & colcount & ")")
.interior.color = 255 'this is the color red
.stopiftrue = true
end with
end with
end with
end sub
when you record a cfr, teh part of excel that translates your actions to code does not know how many cfrs there already are so it makes each the first one so it can continue with configuration and refer to the new cfr as .formatconditions(1)
. you were setting the format configuration to .formatconditions(1)
before setting the second cfr as the top (1) cfr. i prefer to use the with .add method instead.
Source: stackoverflow.com
Related Query
- How to use VBA for conditional formatting on the same range
- How to use VBA to remove 1 (most recent) conditional formatting rule, while leaving the rest intact
- How can I select the active cell and the activecell column with a range for conditional formatting
- How to use a for loop in VBA for multiple conditional formatting
- How to get the background color from a Conditional Formatting in Excel using VBA
- How can I load a VBA library reference and use it in the same procedure?
- How to highlight every instance of a word in the same cell for a given range or selection in Excel 2010?
- How can I use VBA to format Symbols / Icons into cells without using conditional formatting
- How can I use part of a given range in vba (excel) i.e. certain rows in the range
- How to define cell values to the same column for each worksheet in the workbook VBA
- How do I convert the VBA long &HFFFFFFF0 to a uint for use in C#?
- How to use a variable as criteria for the sumifs formula in vba
- How should I Use 1 code for same Objects in VBA Excel
- How can I use VBA to get STDEV of the values for different types of objects listed in multiple rows?
- How do I get a range of cells with an Excel VBA function to calculate individually and not all display the same value?
- how to use missing and boolean values in the same case statement in excel vba
- Choosing dynamic range in VBA - How to adjust the code for a range that changes location in the sheet?
- Is there a VBA function like IsNumeric but for Text? IsText and IsEmpty not working the same when a range is involved
- How to return true if two Range objects have the same range in a for loop?
- How to save the range of all hidden columns in VBA for Excel
- How to dynamically access column properties in a With Range statement for conditional formatting
- In VBA for Word, How can I use the Find method on a hyperlink address?
- VBA function: Find same string multiple times, use offset to generate sum, terminate at the end of dynamic range
- How do I create VBA Macros for MS Word using the .find property that can use variables?
- How to Use "InsertRow" in VBA for a Range of Cells (Excel 2013)
- how to write sumif with changing the criteria and sum range for each row in excel vba
- Excel VBA - How to use the For Each...Next statement
- How to apply a VBA function to a selected range and paste results into the same range?
- How can I use If Error GoTo "specific line" multiple times in the same Excel VBA sub?
- How can I use If Error GoTo "specific line" multiple times in the same Excel VBA sub?
More Query from same tag
- how to copy a ShapeRange.group as picture
- Why do I not need to close SQL Table
- VBA Switching Worksheets Disabled
- Late Binding a UDT in a form module to pass as a parameter
- Declaring Arrays Dynamically in VBA
- How to sum column after the last row of data?
- How to unlink a query from a structured table AND deleting that query in VBA
- Toggle Show/Hidden Sheet
- Multidimensional integer
- How do I capitalize the first letter in every paragraph when there are multiple paragraphs in a cell?
- MsgBox value that starts with 4 above found value.
- How to use instead of Select and ActiveWindow other code?
- Select a previous file in a folder and copy it to a new one
- excel vba loop - repeat a command till somewhere
- How do I capture a specific SQL Server Error In Access VBA That Is Missing the Access Error Code?
- Setting validation via VBA in excel fails when using variable
- ReDim during a loop causing Run-time error 9
- Delete Row If Cell Contains Text After Second Hyphen
- VBA - filter more than one AutoCAD block
- Opening Word Document using VBA in Access 2013
- Excel VBA web scaping element challenge
- Automatically include image and sound files in excel columns, based on a filename in another column
- Loop through cells in range using cell references for the Range
- Excel - VBA - Call path of excel object or current excel.Appliation
- (Excel 2002) Formatting Lost on Pivot Chart Update
- VBA assigning cell references function
- scrape google rhs result using vba
- VBA to check if last cell equals cell on different sheet
- Copy & Pasting values from one Table to another using VBA and ListObjects
- Chart legend entry width