# Yet another conditional format

#### xcellnoob

##### Board Regular
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.

### 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

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<>""

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),""))))``

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)

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.

Does anyone have any ideas for this?

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?

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.

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?

Replies
8
Views
502
Replies
4
Views
142
Replies
4
Views
310
Replies
1
Views
416
Replies
2
Views
483

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.

### Which adblocker are you using?

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

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