Fill D1:D4 format all the way below

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
1,841
Hello,

Here is original data

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">A - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">A - B</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF0000;;">C - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #FF0000;;">C - C</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C - C</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A - B</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A - B</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A - B</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C - C</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A - B</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C - C</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C - C</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Hoja1</p><br /><br />

I want to fill D1:D4 format as per text find all the way below D6 to end of the column

Example after i want it looks like as below

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">A - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">A - B</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF0000;;">C - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #FF0000;;">C - C</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF0000;;">C - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #FF0000;;">C - C</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF0000;;">C - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">A - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">A - B</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF0000;;">C - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF0000;;">C - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">A - B</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">A - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">A - B</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF0000;;">C - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">A - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">A - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF0000;;">C - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">A - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">A - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">A - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">A - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF0000;;">C - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">A - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF0000;;">C - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #FF0000;;">C - C</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">A - B</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #FF0000;;">C - C</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #FF0000;;">C - C</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF00;;">A - A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Hoja2</p><br /><br />

Thank you all

Excel 2000
Regards,
Moti
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Matt Rogers

Well-known Member
Joined
Sep 23, 2011
Messages
1,030
Hi.

Highlight the range, start Conditional formatting and click "Use a formula to ..."

Code:
1  Formula is =D1="C - C"
2  Formula is =D1="C - A"
3  Formula is =OR(D1="A - A",D1="A - B")
Choose the color.
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643
Here is a set of rules for conditional formatting of the entire range:

Green fill =LEFT($D1)="A"
Ref fill =LEFT($D1)="C"
White font =RIGHT($D1)="C"
 

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
1,841
Thank you Matt Rogers, yes conditional formatting is working, but how could it be done by VBA
Because really format is in cells D1:D4 are different as looks let me if I can post as the look

D
1
A - A
2
A - B
3
C - A
4
C - C

<tbody>
</tbody>
Hoja1



Regards,
Moti
 
Last edited:

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
1,841

ADVERTISEMENT

Here is a set of rules for conditional formatting of the entire range:

Green fill =LEFT($D1)="A"
Ref fill =LEFT($D1)="C"
White font =RIGHT($D1)="C"

Thank you Tetra201, yes it works but as you see Post#4

A-A background Green & Font Black
A-B background Green A= black Font & B=Red font
C-A background Red C=White Font & A=Lime Font
C-C background Red & Font White

I think this only could be possible only with VBA

Regards,
Moti
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,151
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Dont have excel at the moment, so is untested

Code:
Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "C").End(xlUp).Row
For r = 6 To lr
    If Range("C" & r).Value = Range("C1") Then
     Range("C1").Copy Range("C" & r)
     ElseIf Range("C" & r).Value = Range("C2") Then
     Range("C2").Copy Range("C" & r)
     ElseIf Range("C" & r).Value = Range("C3") Then
     Range("C3").Copy Range("C" & r)
     ElseIf Range("C" & r).Value = Range("C4") Then
     Range("C4").Copy Range("C" & r)
     End If
    Next r
End Sub
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643

ADVERTISEMENT

Thank you Tetra201, yes it works but as you see Post#4

A-A background Green & Font Black
A-B background Green A= black Font & B=Red font
C-A background Red C=White Font & A=Lime Font
C-C background Red & Font White

I think this only could be possible only with VBA
Here is a tested-to-work code:
Code:
Sub ColorCell()
    For Each cll In Range("D1:D" & Range("D" & Rows.Count).End(xlUp).Row)
        With cll
            Select Case .Value
                Case "A - A": .Interior.Color = vbGreen
                Case "C - C": .Interior.Color = vbRed
                Case "A - B": .Interior.Color = vbGreen
                    .Characters(Start:=5, Length:=1).Font.Color = vbRed
                Case "C - A": .Interior.Color = vbRed
                    .Characters(Start:=1, Length:=1).Font.Color = vbWhite
                    .Characters(Start:=5, Length:=1).Font.Color = vbGreen
            End Select
        End With
    Next cll
End Sub
 

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
1,841
Dont have excel at the moment, so is untested

Code:
Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "C").End(xlUp).Row
For r = 6 To lr
    If Range("C" & r).Value = Range("C1") Then
     Range("C1").Copy Range("C" & r)
     ElseIf Range("C" & r).Value = Range("C2") Then
     Range("C2").Copy Range("C" & r)
     ElseIf Range("C" & r).Value = Range("C3") Then
     Range("C3").Copy Range("C" & r)
     ElseIf Range("C" & r).Value = Range("C4") Then
     Range("C4").Copy Range("C" & r)
     End If
    Next r
End Sub
Thank you Michael M, working as treat all perfect!!

Regards,
Moti
 

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
1,841
Here is a tested-to-work code:
Code:
Sub ColorCell()
    For Each cll In Range("D1:D" & Range("D" & Rows.Count).End(xlUp).Row)
        With cll
            Select Case .Value
                Case "A - A": .Interior.Color = vbGreen
                Case "C - C": .Interior.Color = vbRed
                Case "A - B": .Interior.Color = vbGreen
                    .Characters(Start:=5, Length:=1).Font.Color = vbRed
                Case "C - A": .Interior.Color = vbRed
                    .Characters(Start:=1, Length:=1).Font.Color = vbWhite
                    .Characters(Start:=5, Length:=1).Font.Color = vbGreen
            End Select
        End With
    Next cll
End Sub
Thank you Tetra201, working fine only C-C font is Black instead White

Regards,
Moti
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643
Thank you Tetra201, working fine only C-C font is Black instead White

Regards,
Moti
You are welcome.

The "C - C" font color was black (automatic) on my test sheet,
but here is a modified version that explicitly sets the font color to black:
Code:
Sub ColorCell()
    For Each cll In Range("D1:D" & Range("D" & Rows.Count).End(xlUp).Row)
        With cll
            .Font.Color = vbBlack
            Select Case .Value
                Case "A - A": .Interior.Color = vbGreen
                Case "C - C": .Interior.Color = vbRed
                Case "A - B": .Interior.Color = vbGreen
                    .Characters(Start:=5, Length:=1).Font.Color = vbRed
                Case "C - A": .Interior.Color = vbRed
                    .Characters(Start:=1, Length:=1).Font.Color = vbWhite
                    .Characters(Start:=5, Length:=1).Font.Color = vbGreen
            End Select
        End With
    Next cll
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,754
Messages
5,638,170
Members
417,011
Latest member
Amaden95

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