Conditional Cell Formatting

[Andrew Mackenzie]

Please help if you can.

I have an Excel spreadsheet containing credit ratings from Moody's which are
styled Aaa, Aa1, Aa2, Aa3, A1, A2, A3, Baa1, Baa2, Baa3, Ba1, Ba2, Ba3, B1,
B2, B3, Caa1, Caa2, Caa3.

Ratings from Ba1 onwards are classified "Sub-Investment Grade" and I want to
format these in red to highlight them. What is the easiest way to do this
(no code please!).

Many thanks,

Andrew

 

[Guest]

Hi

If your example is typical, you could try something like Formula is
and then
=CODE(LEFT(A2,1))>65

 

[Jason Morin]

Assuming your ratings are in column A, select col. A, go
to Format > Conditional Formatting, choose "Formula Is"
in the drop-down box, and put:

=AND(LEFT($A1)<>"A",COUNTIF($A1,"Baa*")=0,$A1<>"")

Then press the Format button and format as desired.

HTH
Jason
Atlanta, GA

 

[Jason Morin]

Hi Andy. Your formula was what I initially thought of,
too, but there are 3 higher ratings than Ba1 that begin
with "B". See ya.
Jason

 

[Guest]

Well spotted!!

--
Andy.

Hi Andy. Your formula was what I initially thought of,
too, but there are 3 higher ratings than Ba1 that begin
with "B". See ya.
Jason

 

[ElsiePOA]

One Way:

In Conditional Formatting, Select "Formula Is" and enter
=OR($A1="B1",$A1="B2",$A1="B3",$a1="Caa1",$A1="Caa2",$A1="Caa3")

Then Select Patterns "Red" and that should do i

 

[ElsiePOA]

Whoops!

In my previous post, I left three items out of my Formula - Ba1, Ba2
and Ba3. You can use the formula I showed, but add inside th
parentheses - $A1="Ba1", $A1="Ba2",$A1="Ba3

 

[Andrew Mackenzie]

Fan..bloody..tastic. Both these solutions work a treat.

Now...some of the ratings, but not all, also include a marker at the end to
indicate whether they are on watch for upgrade or downgrade i.e. "Baa3
*-". I want to be able to include for formatting whether or not they are so
marked.

Thanks again.

 

[ElsiePOA]

You can add a second conditional Format with the following formula
=RIGHT($A1,2)="*-"

You might even want to make this a different color because it woul
apply to ANY rating which is being reviewed for chang

 

[Andrew Mackenzie]

Thanks Andy, Jason and Elsie,

I have somehow merged your formulae into one that seems to work (although I
am not sure that I understand why).

Your help is very much appreciated.