Microsoft Office Word Forum - WordBanter

Microsoft Office Word Forum - WordBanter (http://www.wordbanter.com/index.php)
-   Tables (http://www.wordbanter.com/forumdisplay.php?f=4)
-   -   Colour Coding Table Cells in Multiple Tables in One Document (http://www.wordbanter.com/showthread.php?t=156222)

EdG2015 January 26th 15 04:35 PM

Colour Coding Table Cells in Multiple Tables in One Document
 
Hi all,

I've searched all over and can't find something that's giving the results that I want, so any assistance would be greatly appreciated!

I am creating a document in Word 2010 which has around 20 tables in it each of which is of varying size. I need to apply something akin to conditional formatting in Excel which will vary for each table. For example, I want one table to format cells (using excel-type references) C3, F3, I3 and L3 with red text if 80%, amber if 80-99.99% and green if 100%. Another table I want to format anything in row 4 which is a negative as red 0-3 as amber and 4 or more in green.

I know that I could embed excel sheets within the document but this doesn't fit with what I want to achieve, so am looking for a word solution either through VBA or otherwise. If using VBA, would appreciate if someone could point me in the direction of identifying individual tables in the document so the right formatting is applied to the right table. Apologies if this is a pretty dim question - Word is not my natural environment!

Thanks.

EdG2015 January 26th 15 08:03 PM

Hi,

I've managed to resolve by using a nested if function with \*Charformat switch. If anyone else struggles like me I've put the format below. Note use Ctrl+F9 to get the curly brackets ({}]:

{ IF { =(a1/a2)*100 } = 100 { =(a1/a2)*100 \# #% \*Charformat } { IF { =(a1/a2)*100 } 80 { =(a1/a2)*100 \# #% \Charformat } { =(a1/a2)*100 \# #% \*Charformat } } }

Where the underlined section has dark green text and light green background through the font and paragraph formatting, the bold section dark red text and light red background and the bold section dark orange text and yellow background. This replicates the red/amber/green conditional formatting of excel where green is =100%, amber is 80-99.9999% and red is 80%, and the sum is cell A1/A2 (could be replaced with R1C1/R2C1). This can be modified accordingly to allow for multiple variables (up to 20 I believe) and with differing formats and inputs.

Hope this helps someone else who has struggled like I have!

nananaja003 November 24th 15 04:21 AM

I've managed to resolve by using a nested if function with


All times are GMT +1. The time now is 07:41 PM.

Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
WordBanter.com