Yet another conditional format

xcellnoob

Board Regular
Joined
Apr 6, 2005
Messages
188
I know there are 856 topics on conditional formatting, but I really couldn't find anything when I searched that would help me out. I have a spreadsheet that fills in info as it is entered on another sheet in the workbook. What I want is for the cells to not have borders until info is filled in. Easy enough, but there is a formula in each cell, so I don't know how to go about it. any ideas. If you need more clarity, just ask.
Thanks in advance.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

adulted

Active Member
Joined
Jan 22, 2004
Messages
339
Borders or Gridlines

Hi xcellnoob,

I know you were looking for the borders not to be displayed, although I was wondering if possibly you were referring to Gidlines. Gidlines can be temporarily turned of by Tools, Options and remove the check on Gridlines.

Hope this helps,
Paul
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
What sort of formula, is it something that may return a blank, e.g.

=IF(Sheet2!A1="","",Sheet2!A1)

If so then use formula is option in conditional formatting with

=A1<>""
 

xcellnoob

Board Regular
Joined
Apr 6, 2005
Messages
188
Well, since you asked, here is my ugly formula. It is basically applied across 10 or so columns and down 200 rows. So, I want the cells to be outlined if there is a result in the cell. If the cell is "", then no outline. I hope that makes more sense.

Code:
=IF(ISERROR(VLOOKUP(Inputs!L29,Inputs!$A$6:$P$32,2,FALSE)),"",(IF(VLOOKUP(Inputs!L29,Inputs!$A$6:$P$32,2,FALSE)=0,"",IF(N(Inputs!L29),VLOOKUP(Inputs!L29,Inputs!$A$6:$P$32,2,FALSE),""))))
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

Then use the formula I gave above, adjust references where appropriate, e.g. if you range is B2:K200 then select that range with B2 active cell and use formula is

=B2<>""

select borders as required

an alternative formula is

=LEN(B2)
 

xcellnoob

Board Regular
Joined
Apr 6, 2005
Messages
188
Then use the formula I gave above, adjust references where appropriate, e.g. if you range is B2:K200 then select that range with B2 active cell and use formula is

=B2<>""

select borders as required

an alternative formula is

=LEN(B2)
This doesn't seem to work.
 

xcellnoob

Board Regular
Joined
Apr 6, 2005
Messages
188

ADVERTISEMENT

Does anyone have any ideas for this?
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
I don't see why my suggestion doesn't work. Did you try both alternatives?

In what way did it not work? No formatting, wrong formatting?

If you stick to one cell initially, what conditional format did you try and in which cell?
 

xcellnoob

Board Regular
Joined
Apr 6, 2005
Messages
188
Never mind. I keep forgetting that I'm an idiot.
I already had the cells outlined in the draw borders toolbar and that was overriding any conditional formatting that I wanted to apply.
OMG!!
Thanks for your help man. It works perfectly now.
:)
 

xcellnoob

Board Regular
Joined
Apr 6, 2005
Messages
188
BUT!!
Is there a way to do it if the condition is that any cell in the row has data?
If so, outlines, if not, no outlines?
 

Forum statistics

Threads
1,141,098
Messages
5,704,319
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top