formatting (indentation, borders) based on values of 3 cells?

deLockloire

Board Regular
Joined
Apr 4, 2008
Messages
116
Office Version
  1. 365
Platform
  1. Windows
hello, i'd like to format a cell based on the values of 3 cells altogether.

explanation:

i have a sheet arranged in such a way that two tables can fit on a page (when printed). columns A and F have names of items, columns B and G are numeric values, columns C and H contain dates, while column D and I has names of currencies. the two tables (A:D and F:I) are completely separate (visually separated with column E). something like this:

<table style="border-collapse: collapse;" width="500"><tbody><tr><td style="text-align: center; width: 20%; border: 1px solid black;">A</td><td style="text-align: center; width: 10%; border: 1px solid black;">B</td><td style="text-align: center; border: 1px solid black;">C</td><td style="text-align: center; border: 1px solid black;">D</td><td style="text-align: center; width: 10%;">E</td><td style="text-align: center; width: 20%; border: 1px solid black;">F</td><td style="text-align: center; width: 10%; border: 1px solid black;">G</td><td style="text-align: center; border: 1px solid black;">H</td><td style="text-align: center; border: 1px solid black;">I</td></tr><tr><td style="padding-left: 10px; border: 1px solid black;">Item1</td><td style="text-align: right; border: 1px solid black;">26.98</td><td style="text-align: center; border: 1px solid black;">24/1</td><td style="text-align: center; border: 1px solid black;">USD</td><td>
</td><td style="padding-left: 10px; border: 1px solid black;">Item2</td><td style="text-align: right; border: 1px solid black;">55.10</td><td style="text-align: center; border: 1px solid black;">14/1</td><td style="text-align: center; border: 1px solid black;">USD</td></tr><tr><td style="padding-left: 10px; border: 1px solid black;">Item3</td><td style="text-align: right;">45.45</td><td style="text-align: center; border: 1px solid black;">25/1</td><td style="text-align: center; border: 1px solid black;">EUR</td><td>
</td><td style="background: none repeat scroll 0% 0% rgb(242, 221, 220); border-top: 1px solid black; border-bottom: 1px solid black; border-left: 1px solid black;">Header1</td><td style="background: none repeat scroll 0% 0% rgb(242, 221, 220); border-top: 1px solid black; border-bottom: 1px solid black;">
</td><td style="background: none repeat scroll 0% 0% rgb(242, 221, 220); border-top: 1px solid black; border-bottom: 1px solid black;">
</td><td style="background: none repeat scroll 0% 0% rgb(242, 221, 220); border-top: 1px solid black; border-right: 1px solid black; border-bottom: 1px solid black;">
</td></tr><tr><td style="background: none repeat scroll 0% 0% rgb(204, 204, 204); border-top: 1px solid black; border-bottom: 1px solid black; border-left: 1px solid black;">Header2</td><td style="background: none repeat scroll 0% 0% rgb(204, 204, 204); border-top: 1px solid black; border-bottom: 1px solid black;">
</td><td style="background: none repeat scroll 0% 0% rgb(204, 204, 204); border-top: 1px solid black; border-bottom: 1px solid black;">
</td><td style="background: none repeat scroll 0% 0% rgb(204, 204, 204); border-top: 1px solid black; border-right: 1px solid black; border-bottom: 1px solid black;">
</td><td>
</td><td style="padding-left: 10px; border: 1px solid black;">Item4</td><td style="text-align: right; border: 1px solid black;">12.00</td><td style="text-align: center; border: 1px solid black;">16/1</td><td style="text-align: center; border: 1px solid black;">JPY</td></tr><tr><td style="padding-left: 10px; border: 1px solid black;">Item5</td><td style="text-align: right; border: 1px solid black;">94.12</td><td style="text-align: center; border: 1px solid black;">28/1</td><td style="text-align: center; border: 1px solid black;">GBP</td><td>
</td><td style="padding-left: 10px; border: 1px solid black;">Item6</td><td style="text-align: right; border: 1px solid black;">39.45</td><td style="text-align: center; border: 1px solid black;">17/1</td><td style="text-align: center; border: 1px solid black;">USD</td></tr></tbody></table>
now, what i'd like to automatize is the following.
in any given row, if neither B nor C has any value, while A does,

  1. remove indentation in column A (set it back to 0)
  2. color column A:D
  3. remove borders from inside (i.e., no borders between A:D)
similarly, if there are neither G nor H has any value, while F has, do the same, but for the F:I table (i.e., remove indentation in column F, etc.).

i tried to fiddle with conditional formatting, but i could come up with a solution only for action #2. am i missing something?

thanks for your help,
deL
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
You would need to set the conditional formatting for A1, B1, C1, and D1 separately. Basically, select A1 and make the condition something like:

=AND($A1<>"",$B1="",$C1="")

and set the formatting to what you want for A1 only. Then select B1 and enter the same formula but set the formatting to what you want in B, which is different from A.

etc.

Then you can fill the formatting down. I think the trick is having a different Cond. Format set-up for each column's cells.
 
Upvote 0
hey, thanks. i haven't though of that, actually. with your suggestion, i can probably solve action #2 & #3. but i still cannot set the indentation. or at least i can't see any options for that under conditional formatting.
 
Upvote 0
Yeah. I think you can't do that with conditional formatting. Might have to resort to VBA.
 
Upvote 0

Forum statistics

Threads
1,224,222
Messages
6,177,234
Members
452,765
Latest member
Erka Gizli

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