Conditional format - "And" Formula

WeegyAVLover

Distinguished Member
Joined
May 3, 2007
Messages
6,085
Reaction score
2,988
Points
1,872
Location
Scotland
Right,

Been searching for help on this and as yet I have not posted on MrExcel and thought I would try here first.

Here is an extract of what I have and what I want to do.

1 Default Current Allocated
2 A A A
3 B B B
4 A A B
5 A B A
6 B A A
7 B B A
8 B A B
9 A B B

I need to conditionally format cells so that any row ( in this case rows 1 and 2) where all 33 columns match with either the letter A or the Letter B then they are formatted with a Green Fill.

If any columns in the same row do not all match then they need to be formatted with a Red fill.

I am struggling with my formula to create an "And" formula for it?

Hopefully that makes sense and does anyone have any suggestions on the formula?
 
OR?

Or just do 4 lines of conditional formatting
 
Last edited:
Col 1.jpg
Col 2.jpg
 
I would do another hidden column with a count of number of A's.

Then do a conditional formula on the whole row if count A = 33 then Green, otherwise Red

In the conditional formula ensure it is "=$A3=33" (replace A which whichever column it is)

EDIT:
Actually you can use a countif in the conditional format
eg> =(COUNTIF($EX$59:$EZ$59,"A")=33)
 
Last edited:
I'm not very good at conditional formatting :blush:

But maybe conditional formatting based on a formula?

=IF(AND($B$2 = $C$2,$B$2 = $D$2, OR($B$2="A",$B$2="B")),1,0)

Hope this helps in some way lol
 
I'm not very good at conditional formatting :blush:

But maybe conditional formatting based on a formula?

=IF(AND($B$2 = $C$2,$B$2 = $D$2, OR($B$2="A",$B$2="B")),1,0)

Hope this helps in some way lol

Thanks for the help so far guys.

I bandwidth, I had just been trying to sort out my logic and had this and statement:
=AND(E5="A",F5="A",G5="A")

which was producing the right results for when a row had all "A" in them.
But it also meant that anything that did not match this was returning a "false" value which was almost correct except for when something contained all "B" rows.

Now your formula is including an OR which is what I want, but not sure it is quite working 100% correctly. It returned all "1" values when I tested it but it has me on the right tracks so I will see if I can get it there with what you have given me.
 
I got this, but I'm not sure is it's the best way of doing what you require :)

Excel CF #1.png
Excel CF #2.png
Excel CF #3.png
 
Hey Rory...

If default, current and allocated = ALL A or ALL B then all the columns in that row would be highlighted Green. If any of them did not match in a row then the whole Row would be Red.

Ah sorry Col, misread your initial question glad you've got it sorted now anyways! You passing this off to the new employers as a time saving bonus paying feature of you consulting? :D
 
LOL! Yip! sadly it is not quite as straight-forward as conditional formatting.

I need to script output on a VNX to provide information on trespassed LUNS... Anyone who does not have a clue about what I am talking about please do not ask for more information... It really is that dull and boring, except to someone like me.
 
LOL!
I am reading the manual but I can get the output its getting it into a format that can be easily put into a tracking spreadsheet.
What would be great to know is if there is a way to combine 2 text files into 1 text file where each line from each file is seperated by a tab.

At the moment google is failing me. Been a while since I have used DOS loads and it just is not as powerful as UNIX/Linux.
 
LOL!
I am reading the manual but I can get the output its getting it into a format that can be easily put into a tracking spreadsheet.
What would be great to know is if there is a way to combine 2 text files into 1 text file where each line from each file is seperated by a tab.

At the moment google is failing me. Been a while since I have used DOS loads and it just is not as powerful as UNIX/Linux.

Dos this post help?

Merge Two text files line by line using batch script - Stack Overflow

Just insert a comma instead of a space in the output to the "c" file in the example?
 

The latest video from AVForums

TV Buying Guide - Which TV Is Best For You?
Subscribe to our YouTube channel
Back
Top Bottom