Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 93
  • Last Modified:

Conditional formatting

I have 5 columns, with a header.
There are two basic types of rows - those that have a code of XXXXXX-00 and those that don't.
I want all of these rows (entire row) to be a distinguishable color and the alternates to be some slightly different color.
I could get it working with just the leading cell (col A) only, but it wouldn't highlight the entire row.
Is there a way to setup conditional formatting to do this?  Wanted to stay away from a macro solution if possible...
0
sirbounty
Asked:
sirbounty
3 Solutions
 
tomfarrarCommented:
http://www.howtogeek.com/howto/45670/how-to-highlight-a-row-in-excel-using-conditional-formatting/

See paragraph starting with:

"Now that we have a working formula, let’s apply it across our entire table. As you can see above, the formatting applies only to the cell we started off with. Click the button next to the Applies to field and drag the selection across your entire table."
0
 
ProfessorJimJamCommented:
You need to select the entire region then put the formula, your formula doesn't work because you didnot select the entire region I mean entire rows
0
 
NorieData ProcessorCommented:
Select all the rows.

Goto conditional formatting.

Select Use formula to...

Enter this formula, changing 2 to the first row in the rows you selected.

=$A2=" XXXXXX-00"

Format as required.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
sirbountyAuthor Commented:
I thought I had the first column working, but now it's highlighting the opposite data.
My formula is
=RIGHT($A2,3)="-00"
but these cells don't get highlighted as I've set it... : \
0
 
sirbountyAuthor Commented:
Weird - even setting the formula to =A2 doesn't change that single cell... wonder if I've done something somewhere... :(
0
 
sirbountyAuthor Commented:
Somehow I fixed that, but it's still not formatting the rows... still trying.
0
 
sirbountyAuthor Commented:
Ok, I've determined that setting a conditional format formula fails:
=RIGHT(TRIM($A$2),3) (just trying to grab the "-00" which doesn't seem to work either.

If I use the text, ends with =RIGHT(TRIM($A$2),3), it will highlight the first column, but even selecting the range does not format the rows...
0
 
sirbountyAuthor Commented:
=RIGHT(TRIM($A2),3)="-00" finally worked for me - had that extra $ in there...oops. :^)
0
 
tomfarrarCommented:
RIGHT(TRIM($A$2),3)......  Your $A$2 is selecting only A2.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now