Conditional Formatting (XL 2003/2010) 1

[ladyck3]

I am using 2010, hubby refuses to upgrade or get the conversion program so I'm stuck saving my work as an XLS file rather than XLSX, due to the differences, not all features of 2010 will work when saving a file due to compatibility issues.

That said...

I have a list our movies... some DVD and some Blu-ray. My listing has a column which lists which media type the movie in our collection happens to be but we have duplicates, many of them in both media types. We are culling our collection and I'd like to ... conditionally format? The titles in the listing.

If the media type, in column B says Blu-ray, I would like the title in Column A to show it visually, by the title appearing in a blue cell. I want to hide the media type column so it will be easy to tell just by the color of the cell.... sorting alphabetically we can easily tell which of the same is either DVD or Blu-ray.

I've tried a few things that just don't work. I'm not a VBA person, I'm hoping there is some sort of formatting that I'm not aware of, that will fit the bill....

Any suggestions?

Thank you in advance...
Laurie


ladyck3
aka: Laurie

 

[SkipVought]

Hi,

Do the CF in 2003. That way both can work.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Select the RANGE you want to format.

Format > Conditional Formatting.

Select the Formula option.

Your expression will be written with the TOP LEFT CELL of your selection RANGE. I'm guessing the column A is you selected RANGE. If A2 is the TOP LEFT CELL then you expression would be
[tt]
=B2="Blu-ray"
[/tt]
And then assign a FORMAT (button) as desired.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[ladyck3]

Ok, as per usual, you may have lost me here.

I have almost 2000 lines.... (many series with each disc cataloged)...

I highlighted A:A (as the range) then used the formula you suggested by using New Rule the formula option... Entered the formula you provided and VOILA sort of...

The problem is that B:B can be any type DVD, HD DVD, Blu-ray... I only want the titles in A that show "Blu-ray" in B ...

I listed line numbers in my initial request to explain... I am hoping I can get the formula to recognize when Blu-ray is present in B:B any title next to that will also be blue.

Thing is, is random with the information provided... as it is pointing to a specific cell. and if I use the formula now showing B2, to B:B well that's random, right?

Hi Skip... good to see you

=====================================================================
10 Things I Hate About You DVD
12 Monkeys HD DVD
12 Years a Slave Blu-ray
13th Warrior, The DVD
1776 DVD
2 Broke Girls: The Complete First Season Blu-ray
2 Broke Girls: The Complete First Season: Disc One Blu-ray
2 Broke Girls: The Complete First Season: Disc Two Blu-ray
2 Fast 2 Furious Blu-ray
2001: A Space Odyssey Blu-ray
2001: A Space Odyssey Blu-ray

From the list above all in bold are the cells in A which are now blue...but some are DVD and still blue, some are Blu-ray and not blue...


Laurie

ladyck3
aka: Laurie

 

[SkipVought]

So you selected column A

Then opened the Conditional Format Wizard

Selected the Use Formula option

Entered the expression...
[tt]
=B1="Blu-Ray"
[/tt]
And assigned a format in the FORMAT button

Right?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[ladyck3]

Well yes, I just did and of course.. you know all...
It worked!

Sometimes it will list DVD, Blu-ray Any way to have it "see" Blu-ray in the cell in Column B and color the title in A?

I'm not really THAT concerned about it, was just wondering

Thanks Skip.... I appreciate you!

Laurie

ladyck3
aka: Laurie

 

[SkipVought]

Well you never said that other stuff was in the same cell with Blu-Ray..
[tt]
=Find(B1,"Blu-Ray")>0
[/tt]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[ladyck3]

I didn't say that because I didn't realize it until I was going down the list.
The APP that I use DVD Profiler Pro ... you enter the UPC code into the Add to Owned list and the rest is automatic, it pulls it from a database somewhere.

I have an add-in that exports the file into csv format and from there I work my magic (or your magic in this instance). LOL

Thanks Buddy... man I owe you!

OOPS that didn't work, it colored all titles blue. Hey... I'm fine with what I have, honest...

ladyck3
aka: Laurie

 

[SkipVought]

Sorry, I just got home to test my formula...
[tt]
=FIND("Blu-ray",B1)
[/tt]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[ladyck3]

WOO HOO that did it.... you are a ROCK STAR

ladyck3
aka: Laurie