score:3

Accepted answer
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.


Related Query

More Query from same tag