Override conditional formatting

[generalgort]

Hi All,

My first post here, I am a bit of a novice at excel and a complete novice at VB and VBA so be patient with me.

It seems a relatively simple problem, but I can't seem to find an answer.

I am using Excel 2002 on XP with SP3.

I have a large spreadsheet that for ease of reading I have shaded alternate rows using conditional formatting. OK so far, I have three columns that I want to have this shading until a 'tick' is entered in a cell in those columns then a colour is displayed. I am using the 'tick' character in 'Wingdings' font from the character map which I copy and paste.

As I said it seems a relatively simple problem, but at the moment the solution eludes me, can anybody help please?

Phil

 

[xld]

You would need to test for that value in your CF formula, if you use WEbdings and the letter a that makes the test simple.

 

[Magriza]

But remember to test it as the first condition as conditional formatting will stop testing once a TRUE criterium as been met.

 

[generalgort]

Thanks for the reply xld & Magriza,

Do you mean use an 'IF' statement, I tried using:

=IF(C5="",(MOD(ROW(),2)=0),0)

as Condition 1 and:

=IF(C5<>"",(MOD(ROW(),2)=0),0)

for Condition 2, where C5 is the first cell that I want to change to a colour when a 'tick' is entered, when I 'fill down' it works OK but only for the shaded cells and not for the unshaded ones, wher am I going wrong?

Phil.

 

[xld]

No, more of the form

=NOT(AND(C5="",MOD(ROW(),2)=0))

 

[generalgort]

Hi xld,

At the risk of showing how little I understand about excel functions, could you please explain how this function works and how I get the cell to change colour, I have tried inserting it in the conditional formatting and it results in C5 changing from white to shaded and C6 changing from shaded to white etc. if I enter data into the cells then the cells all become shaded, the format colour in the conditional formatting. I would like the colour to be different for each of the three columns, ie, yellow, green, & red and different from the shading, pale blue.

(I don't think it was so much a risk as a certainty)

Phil.