The rules are not additive.
The first rule that applies wins and the others are ignored. So you'll have to
rewrite your rules so that all the formatting you want to apply is specified in
that condition.
Debra Dalgleish shares lots of tips:
http://contextures.com/xlCondFormat01.html
I think this may be what you want.
My activecell is B5 and I formatted it based on what's in B5:
The first rule:
=OR(ISTEXT(B5),AND(ISNUMBER(B5),LEFT(CELL("format",B5),1)<>"D"))
I gave this a pattern with no border.
My second rule:
=AND(ISNUMBER(B5),LEFT(CELL("format",B5),1)="D")
I gave this the same pattern color, but added the border.
Watch out, though. Dates and numbers are the same thing to excel. If that cell
can contain a date or a number, you may have to reformat it after toggling
between them.
davegb wrote:
>
> On Mar 28, 2:17 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > I created a single rule that would apply a pattern (fill color) and a border.
> >
> > And I could see the border. Maybe the color of the border is too close to the
> > color of the pattern?
> >
> > Or maybe you're using multiple rules and you have to add the border to each of
> > the formats?
> >
> > I don't really know what you want.
> >
> >
> The border and pattern are very different colors. I can see them both
> if I apply a single condition to get them.
>
> I figured that one out. The problem is, I want the pattern in any cell
> that has numbers or text it it, which I can do. But I also want, in
> the same cells, a condtional border when cell A1 has a date in it
> (which is all they will ever have). I can get the cells, using
> conditional formatting, to do one or the other, or even both, if every
> cell needs both. But I only want the borders to appear if there's a
> date in col A. If Col A is blank, no borders in the adjacent cells in
> Col B and C, just the pattern. It seems to me there have to be 2
> separate conditons for 2 separate criteria. (And it may be order
> dependent, I don't know how conditional format handles that). So one
> condition would specify that if the current cell has text, add a
> pattern. The other condition is that if Col A in the same row has a
> date (number, non-blank) in it, add a border and keep the pattern.
>
> I can write the conditional formatting to do both with one condition,
> or to do either with 2 conditions, but not to show both a border and a
> pattern, each based on a separate condition. If the pattern is shown,
> the border is hidden. Like the pattern condition is over-riding the
> border condition. I want them to be additive, not exclusive. Is that
> any clearer?
>
> Thanks for the help. It may be I'm just trying to do something XL
> doesn't do. You've helped me before and I've read many of your posts,
> so if you don't know how to do it, Dave, it probably can't be done!
>
> >
> >
> >
> > davegb wrote:
> >
> > > On Mar 28, 12:16 pm, "davegb" <dav...@safebrowse.com> wrote:
> > > > On Mar 28, 8:51 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> >
> > > > > Just use the adjacent cell's address in the formula:
> >
> > > > > If A1 is the activecell:
> > > > > formula is:
> > > > > =B1<>""
> > > > > or
> > > > > =B1=""
> >
> > > > > depending on what you want.
> >
> > > > > Or add it to an existing rule:
> > > > > =and(B1<>"",yourformulahere)
> >
> > > > > davegb wrote:
> >
> > > > > > On Mar 28, 6:08 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > > > > > In code?
> >
> > > > > > > dim myCell as range
> > > > > > > set mycell = activesheet.range("b99")
> > > > > > > if isempty(mycell.offset(0,1).value) then
> > > > > > > msgbox "the cell to the right is empty--no formula, no nothing!"
> > > > > > > end if
> >
> > > > > > > If you want to check to see if it contains a formula that evaluates to "" or is
> > > > > > > empty:
> >
> > > > > > > if mycell.offset(0,1).value = "" then
> >
> > > > > > > davegb wrote:
> >
> > > > > > > > I want to test to see if an adjacent cell is blank or not to change
> > > > > > > > the formatting of the cell in question. I would guess it might involve
> > > > > > > > the Offset property. Does anyone know how to do this?
> >
> > > > > > > > Thanks!
> >
> > > > > > > --
> >
> > > > > > > Dave Peterson
> >
> > > > > > Thanks for your reply, Dave. I know how to do it in code, I want to
> > > > > > know how to do it in conditional formatting.
> >
> > > > > --
> >
> > > > > Dave Peterson- Hide quoted text -
> >
> > > > > - Show quoted text -
> >
> > > > Thanks for your reply. I tried putting in =AND($A2<>"") and then put
> > > > it to format it with an underline, but it doesn't work.
> >
> > > > Let me explain what I'm trying to do. I'm keeping a list of daily
> > > > activities. In col A I put the date. In Col B I put a description of
> > > > what I did. In Col C, I put it who I did it for. There can be any
> > > > number of entries in Cols B and C for every date entry in Col A. I
> > > > want Col A to change to a different color when there is a date in that
> > > > cell, and I want a border on the top edge of that cell to separate it
> > > > from the previous day's list. Then, in Cols B and C, I want them to be
> > > > a different color and to put an edge on the top of the cell when
> > > > there's a date in Col A. So as I add new activites and persons to the
> > > > list, the cell colors change from background to a selected cell
> > > > shading. Only when there is a date in Col A do I want Col A, B and C
> > > > to have a border at the top of all 3 cells as a separator.
> >
> > > > I can get one thing or the other to happen, change cell color or put a
> > > > border at the top, with conditional formatting, but when I try to do
> > > > both, the border disappears.
> >
> > > > I hope this is clearer than before.- Hide quoted text -
> >
> > > > - Show quoted text -
> >
> > > I just went back and tried some more things. I figured out that when a
> > > cell is conditionally shaded, the borders, if conditionally placed,
> > > dissappear. So I can put in the condition to create a border, but if I
> > > also try to shade the cell, the border dissappears. Is this a glitch
> > > or by design? More importantly, is there a way around it?
> >
> > --
> >
> > Dave Peterson- Hide quoted text -
> >
> > - Show quoted text -
--
Dave Peterson
Similar Threads
