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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
 
Upvote 0
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<>""
 
Upvote 0
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),""))))
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
:)
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,219,101
Messages
6,146,288
Members
450,685
Latest member
frederik00

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
Back
Top