How to test an adjacent cell for blank in conditional formatting

D

davegb

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!
 
D

Dave Peterson

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
 
D

davegb

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
Click to expand...

Thanks for your reply, Dave. I know how to do it in code, I want to
know how to do it in conditional formatting.
 
P

Pete_UK

Imagine the cells you want to test are in column A and you want to
change the format in column B. Highlight all the cells in column B,
with B1 as the active cell. Then Format | Conditional Formatting and
in the first box select Formula Is from the pull-down, then in the
next box enter this formula:

=A1=""

Then click the format button, click the Patterns tab and choose your
colour. Click OK twice and the CF is set for all the cells you
highlighted.

Hope this helps.

Pete
 
D

Dave Peterson

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)
 
D

davegb

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)







--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...

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.
 
D

davegb

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 -
Click to expand...

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?
 
D

Dave Peterson

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.
 
D

davegb

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.
Click to expand...
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!
 
D

Dave Peterson

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.
 
D

davegb

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.
Click to expand...

Thanks for your reply, Dave. I'm beginning to realize I'm in way over
my head. I have no idea what those formulas do, much less how to
modify them for my situation. I think I'll just continue to manually
format the spreadsheet. I appreciate your efforts.
 
Top