change format as row changes values

[nemo121]

I've been searching this site but couldn't find anything similar to what I'm trying to accomplish, so here's my post.

I have a set of values that a user can input in a row, A1:A20. If A1 is equal to A2, no format is required. But if A3 is different then A2 then conditional formatting would occur. If A4 A5 A6 has the same value as A3 the format should be the same throughout, but as soon as the next cell changes, A7, a new conditional format should apply. These conditions should apply through 20 cells and the format should change the shading of a color as a cell change.

A1 A2 A3 A4 A5 A6 A7
1 1 2 2 2 2 1

So A1 and A2 would be black font
A3,4,5, and 6 would be light red
A7 would turn red
and if a change occurs again font would return as dark red and so on.

Hope this is as detailed as it gets without confusion. thanks!

 

[UniMord]

I have a set of values that a user can input in a row, A1:A20.

You mean column, I suppose?

Anyway, Conditional Formatting can't do it. There is a limit of 3 conditions, plus there is no way to detect the color of a cell and increment it. I'm sure this can be easily handled in VBA, but I'm not sufficiently up on it just yet.

That said, I can offer the following unsightly approach to have it cycle through 3 different colors.

Column A2:A21 contains your values. Not starting in A1 simplifies matters.

Column B2:B21 contains the following formula: =A2=A1 (Fill down to increment.)

You can hide this column if you like.

Select A2:A21, and in Conditional Formatting, you set the following 3 conditions, each with the color of your choice:

Condition 1: Formula Is =MOD(COUNTIF(B$2:B2,"=FALSE"),3)=0
Condition 2: Formula Is =MOD(COUNTIF(B$2:B2,"=FALSE"),3)=1
Condition 3: Formula Is =MOD(COUNTIF(B$2:B2,"=FALSE"),3)=2

I hope this helps.

 

[Greg Truby]

You don't indicate which version of Excel you are running, but assuming better'n XL97...

In a standard module (see below for instructions on adding code if this is new):

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Enum</SPAN> XlColorIndexes       <SPAN style="color:#007F00">' XlColorIndex already used by Excel</SPAN>
    xlciBlack = 1
    xlciWhite           <SPAN style="color:#007F00">'  2</SPAN>
    xlciRed             <SPAN style="color:#007F00">'  3</SPAN>
    xlciBrightGreen     <SPAN style="color:#007F00">'  4</SPAN>
    xlciBlue            <SPAN style="color:#007F00">'  5</SPAN>
    xlciYellow          <SPAN style="color:#007F00">'  6</SPAN>
    xlciPink            <SPAN style="color:#007F00">'  7</SPAN>
    xlciTurquoise       <SPAN style="color:#007F00">'  8</SPAN>
    xlciDarkRed         <SPAN style="color:#007F00">'  9</SPAN>
    xlciGreen           <SPAN style="color:#007F00">' 10</SPAN>
    xlciDarkBlue        <SPAN style="color:#007F00">' 11</SPAN>
    xlciDarkYellow      <SPAN style="color:#007F00">' 12</SPAN>
    xlciViolet          <SPAN style="color:#007F00">' 13</SPAN>
    xlciTeal            <SPAN style="color:#007F00">' 14</SPAN>
    xlciGrey25          <SPAN style="color:#007F00">' 15</SPAN>
    xlciGrey50          <SPAN style="color:#007F00">' 16</SPAN>
    xlciSteel           <SPAN style="color:#007F00">' 17</SPAN>
    xlciCiruelo         <SPAN style="color:#007F00">' 18</SPAN>
    xlciEggShell        <SPAN style="color:#007F00">' 19</SPAN>
    xlciLightCyan       <SPAN style="color:#007F00">' 20</SPAN>
    xlciDarkPurple      <SPAN style="color:#007F00">' 21</SPAN>
    xlciSalmon          <SPAN style="color:#007F00">' 22</SPAN>
    xlciCeleste         <SPAN style="color:#007F00">' 23</SPAN>
    xlciGrey20          <SPAN style="color:#007F00">' 24</SPAN>
    xlciAzulOscuro      <SPAN style="color:#007F00">' 25</SPAN>
    xlciMagenta         <SPAN style="color:#007F00">' 26</SPAN>
    xlciAmarillo        <SPAN style="color:#007F00">' 27</SPAN>
    xlciCyan            <SPAN style="color:#007F00">' 28</SPAN>
    xlciPurple          <SPAN style="color:#007F00">' 29</SPAN>
    xlciRojoOscuro      <SPAN style="color:#007F00">' 30</SPAN>
    xlciDarkCyan        <SPAN style="color:#007F00">' 31</SPAN>
    xlciAzul            <SPAN style="color:#007F00">' 32</SPAN>
    xlciSkyBlue         <SPAN style="color:#007F00">' 33</SPAN>
    xlciLightTurquoise  <SPAN style="color:#007F00">' 34</SPAN>
    xlciLightGreen      <SPAN style="color:#007F00">' 35</SPAN>
    xlciLightYellow     <SPAN style="color:#007F00">' 36</SPAN>
    xlciPaleBlue        <SPAN style="color:#007F00">' 37</SPAN>
    xlciRose            <SPAN style="color:#007F00">' 38</SPAN>
    xlciLavender        <SPAN style="color:#007F00">' 39</SPAN>
    xlciTan             <SPAN style="color:#007F00">' 40</SPAN>
    xlciLightBlue       <SPAN style="color:#007F00">' 41</SPAN>
    xlciAqua            <SPAN style="color:#007F00">' 42</SPAN>
    xlciLime            <SPAN style="color:#007F00">' 43</SPAN>
    xlciGold            <SPAN style="color:#007F00">' 44</SPAN>
    xlciLightOrange     <SPAN style="color:#007F00">' 45</SPAN>
    xlciOrange          <SPAN style="color:#007F00">' 46</SPAN>
    xlciBlueGrey        <SPAN style="color:#007F00">' 47</SPAN>
    xlciGrey40          <SPAN style="color:#007F00">' 48</SPAN>
    xlciDarkTeal        <SPAN style="color:#007F00">' 49</SPAN>
    xlciSeaGreen        <SPAN style="color:#007F00">' 50</SPAN>
    xlciDarkGreen       <SPAN style="color:#007F00">' 51</SPAN>
    xlciOliveGreen      <SPAN style="color:#007F00">' 52</SPAN>
    xlciBrown           <SPAN style="color:#007F00">' 53</SPAN>
    xlciPlum            <SPAN style="color:#007F00">' 54</SPAN>
    xlciIndigo          <SPAN style="color:#007F00">' 55</SPAN>
    xlciGrey80          <SPAN style="color:#007F00">' 56</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Enum</SPAN>
</FONT>
*--------------------------------*
To add code to a workbook:

1. Alt+F11 to get to the Visual Basic Editor (VBE).
2. From the VBE menu Insert|Module
3. Add in code (copy and paste).
4. Alt+F11 again to jump back to Excel.
5. Alt+F8 then pick the macro from the list.

Note: It's usually a good idea to save your work before running new macros.<hr>If you wish to always have the macro available and not just when one particular workbook is open - i.e. the macro is a "generic" macro; it is better to place the macro in your Personal Macro Workbook. This is a workbook that Excel automatically creates. If you use the Macro Recorder to record a macro, the dialog box asks where you want to store the macro: (a) the active wb, (b) a new wb or (c) in your Personal Macro wb. If you select Personal Macro Workbook and you have not previously recorded a macro there, Excel will create it for you, naming it Personal.xls and placing it in your Excel startup directory. This way every time you start Excel, Personal.xls will load and its macros will be available to you. (Note that Personal.xls is a hidden workbook and not an Excel Add-In.)<hr /><hr />

In the worksheet's code module (see below for instructions if you have not added code to a worksheet's code module before):

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#007F00">' [A2:A20] is a named range, "rngToCheck"</SPAN>
    <SPAN style="color:#007F00">' (note that the range starts on 2nd row, not 1st)</SPAN>
    
    <SPAN style="color:#00007F">If</SPAN> Intersect(Target, [rngToCheck]) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    
    <SPAN style="color:#00007F">Dim</SPAN> rngCell <SPAN style="color:#00007F">As</SPAN> Range, intChgCounter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Static</SPAN> eColors(20) <SPAN style="color:#00007F">As</SPAN> XlColorIndexes, booDefined <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>
    
    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> booDefined <SPAN style="color:#00007F">Then</SPAN>
        eColors(0) = xlciBlack
        eColors(1) = xlciPink
        eColors(2) = xlciRed
        eColors(3) = xlciDarkRed
        eColors(4) = xlciBlue
        eColors(5) = xlciBlueGrey
        eColors(6) = xlciBrightGreen
        eColors(7) = xlciBrown
        <SPAN style="color:#007F00">' ...and so on through twenty...</SPAN>
        booDefined = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rngCell <SPAN style="color:#00007F">In</SPAN> [rngToCheck]
        <SPAN style="color:#00007F">If</SPAN> rngCell <> rngCell.Offset(-1) <SPAN style="color:#00007F">Then</SPAN> intChgCounter = intChgCounter + 1
        rngCell.Font.ColorIndex = eColors(intChgCounter)
    <SPAN style="color:#00007F">Next</SPAN> rngCell
        
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>
*---------------------------------*
To add code to a worksheet:

1. In Excel (not the VBE), right-click the tab for the sheet to which you want to add the code.
2. Click on View Code... from the popup menu.
3. Copy and Paste code or
   <ul type=disc>
4. Pick Worksheet from the left combobox at the top of the code pane
5. If SelectionChange (the default) is not the correct event, then click the appropriate event from the combobox at the right.

[/list]If you are already in the VBE you can doubleclick on the sheet's icon+name in the project explorer window (Ctrl+R if the project explorer is not visible). If the title bar of the VBE says "Microsoft Visual Basic - yourworkbookname.xls - [yoursheetname (Code)]" then you are in the right spot.

 

[UniMord]

Although Greg's macro is topnotch, if you still want to go the Conditional Formatting route, it just occurred to me, that we can squeeze in yet a fourth color.

Follow my instructions above, with the single change of changing the MOD in the Conditional Formats, from 3 to 4.

Condition 1: Formula Is =MOD(COUNTIF(B$2:B2,"=FALSE"),4)=0
Condition 2: Formula Is =MOD(COUNTIF(B$2:B2,"=FALSE"),4)=1
Condition 3: Formula Is =MOD(COUNTIF(B$2:B2,"=FALSE"),4)=2

What this will accomplish is that when the MOD equals 3, it'll use the default color for the cell.