Conditional Format a row based on a value in a cell

[kaseyleigh]

Hi,

I have a data from an external source (the web) in range A4:G433

in Column H = Team A, Column J = Team B an X in a cell will show players picked for each Team

I have tried to apply Conditional Formatting (CF) to the data but each time it is refreshed the range in the 'Applies to' dialog box of CF is changed ... I have no idea why, very annoying!!! Else, it would have worked fine.

So, I turn to VBA change event, but not sure how to write the code.

What I need is, examples below :-

If there is an X anywhere in Col H (Team A), I would like cells in the same row A:H to be highlighted green.
So, if H10 contains X, A10:H10 will be green

If there is an X anywhere in Col J (Team B), I would like cells in the same row A:J to be highlighted purple.
So, if J233 contains X, A233:J233 will be purple

I hope this makes sense.

Thank you!

[NBVC]

What formulas did you use for Conditional Formatting?

[kaseyleigh]

I used =$H4="X" for green =$J4="X" for purple
and applied to range =$A$4:$J$433 but this range change to =$H$4:$J$433 each time I refreshed the data!!!!

[NBVC]

That strange... do you have other conditional formatting in some of those cells that weren't previously cleared?

is it possible to upload the sheet here for us to review?

[kaseyleigh]

Sure, here you go ....

Look at the CF range then R Click on data then click refresh you will see the CF range alter.

[NBVC]

It seems to work fine when I open it and close it in Excel 2007. Perhaps a bug in your 2010 version?

Hopefully someone with access to 2010 currently can test to confirm....

[Cutter]

OK, I've had a look. The CF range was adjusting.
You need to right click in your web query area and put a check mark in "Preserve cell formatting".
I also selected "Overwrite existing cells with new data, clear unused cells"
The CF range now does not adjust after refreshing.

PS - poor old Tiger - nobody picks him anymore.

[kaseyleigh]

Cutter, thank you so much !!!

[JosephP]

right-click the data table, choose 'data range properties' and check the 'preserve cell formatting' option.

ne'er mind - shoulda refreshed. ;-)

[Cutter]

You're welcome.

[NBVC]

The part that it was a web query didn't click in my little head ;(... so didn't realize that was the issue... thanks for the assist guys.