# Conditional formating

1. Hi again,

After a little thought you could use this in a cell to combine both checks for the conditional formula.

Inserted into cell B7,
=IF(B2="W","",IF(B2="L",IF(B3<8,2,"")))

Inserted into cell B8,
=IF(B4="W","",IF(B4="L",IF(B5<8,2,"")))

This is looking at both cells (B2 & B3) & (B4 & B5) for the comparison checks. Change cell references (B2 & B3) & (B4 & B5) for each cell you want to check.

The conditional formula for cell B2 is =B7=2, for the range of your cells.

The conditional formula for cell B4 is =B8=2, for the range of your cells.
My Computer

2. Lottiemansion said:
Inserted into cell B7,
=IF(B2="W","",IF(B2="L",IF(B3<8,2,"")))
Inserted into cell B8,
=IF(B4="W","",IF(B4="L",IF(B5<8,2,"")))
If you need the Highlight for cell value above "8", then the formulas should be: -

Inserted into cell B7,
=IF(B2="W","",IF(B2="L",IF(B3>8,2,"")))

Inserted into cell B8,
=IF(B4="W","",IF(B4="L",IF(B5>8,2,"")))

I hope it helps you...
My Computer

3. I have just tried the advice and when I enter into a cell =if(A4<8,1,0) it works ok but sometimes the cells I am checking are blank and it will put a 1 in the result, how can I resolve this please.
Len
My Computer

I have just tried the advice and when I enter into a cell =if(A4<8,1,0) it works ok but sometimes the cells I am checking are blank and it will put a 1 in the result, how can I resolve this please.
Len
If you change the cell to this: -

=if(A4="","",if(A4<8,1,0))

It should keep the cell blank.
My Computer

5. Hi donnylad. I found a formula on the web that will do just what you want without adding any extra cells but it requires that your data be arranged in columns rather than rows. If you are willing to use the Transform function to do this I can adapt what I found to give you the results you are asking for. Let me know if you can live will columns in lieu of rows and I’ll post the formula later today.
Bob
My Computer

6. Not sure ifI can Bob I am going to try what I have up to now but you can post it and I will see if it will do my needs.
Thanks
Len
My Computer

7. Hi Lottie, Thanks for your help its working fine.
Len
My Computer

Not sure ifI can Bob I am going to try what I have up to now but you can post it and I will see if it will do my needs.
Thanks
Len
Here you go:

The reason that the data needs to be arranged into columns is that the relative addressing that is used with conditional formatting does not work when the comparisons are made across rows. Maybe someone else knows how to modify the formula but I haven't yet discovered how.

BTW here's where I found the formula: Use Conditional Formatting with Multiple Conditions - YouTube
In case the video won't play just search on "Youtube Use Conditional Formatting with Multiple Conditions"
Bob
My Computer

9. Thanks Bob I am looking at it now and reply.
Len
My Computer

10. Hi,

If the conditional formula above (BOB's) is changed to this: -

=AND(B2="L",B3<8) for row one, applying to your cells

and

=AND(B4="L",B5<8) for row two, applying to your cells.

It does work across the spread sheet. However, the problem of highlighting an empty cell still exists as this equates to zero or a null.

Therefore use this formula: -

=IF(B3="","",AND(B2="L",B3<8)) for row one.

and

=IF(B5="","",AND(B4="L",B5<8)) for row two. Sorted I think?

Attachment 221352
My Computer

Page 2 of 3 First 123 Last

excel conditional formating in Microsoft Office and 365
Hi to all,,, I am using excel 2016 and I have a 2 rows of data of which the top row consists of W or L and the row underneath consists of numbers that can be a number from 1 to 8. If the top cell is an L and the cell below is >6 I want the selected...
Formating New Harddisk in Drivers and Hardware
Hello, I think this will be sortof stupid question but still I'll ask. I want to add a hard disk to my pc just for data. The disk was removed from my old pc after running DBAN to clean it up. I do not want to divide it into partitions, just one...
Excel Conditional formatting question. in Microsoft Office and 365
If I wanted to make a conditional formatting rule that would select all cells in a column with a formula generated value less than 0.9 and greater than 1.1, ignoring all empty cells, what formula would I use? Thanks.
partioning/formating in Drivers and Hardware
I resized my win10 500gb hdd --should I format the unallocated space now or when I go to use it?
Hello, Need to undertake the above for my daughter's college course. Haven't a clue where to start and I am not particularly technical (understatement). Have found a lot stuff here about merits of FAT and exFat, but just need a working how to....
Our Sites