Conditional formating Solved

Page 2 of 3 FirstFirst 123 LastLast
  1.    4 Weeks Ago #11

    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.

    Click image for larger version. 

Name:	Excel Conditional Formating.JPG 
Views:	1 
Size:	46.2 KB 
ID:	221269

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

Name:	Condition1 Formating.JPG 
Views:	2 
Size:	53.4 KB 
ID:	221273

    The conditional formula for cell B4 is =B8=2, for the range of your cells.
    Click image for larger version. 

Name:	Condition2 Formating.JPG 
Views:	2 
Size:	52.6 KB 
ID:	221272
      My ComputerSystem Spec

  2.    4 Weeks Ago #12

    Lottiemansion said: View Post
    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 ComputerSystem Spec

  3.    4 Weeks Ago #13

    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 ComputerSystem Spec

  4.    4 Weeks Ago #14

    donnylad said: View Post
    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 ComputerSystem Spec

  5.    4 Weeks Ago #15

    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 ComputerSystem Spec

  6.    4 Weeks Ago #16

    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 ComputerSystem Spec

  7.    4 Weeks Ago #17

    Hi Lottie, Thanks for your help its working fine.
    Len
      My ComputerSystem Spec

  8.    4 Weeks Ago #18

    donnylad said: View Post
    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:
    Click image for larger version. 

Name:	ExcelFormat.JPG 
Views:	13 
Size:	51.6 KB 
ID:	221313
    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 ComputerSystem Spec

  9.    4 Weeks Ago #19

    Thanks Bob I am looking at it now and reply.
    Len
      My ComputerSystem Spec

  10.    4 Weeks Ago #20

    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
    Attached Thumbnails Attached Thumbnails Excel Conditional Formating 2.JPG  
      My ComputerSystem Spec


 
Page 2 of 3 FirstFirst 123 LastLast

Related Threads
Solved 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...
Solved 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.
Solved 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
Site Links
About Us
Windows 10 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 10" and related materials are trademarks of Microsoft Corp.

© Designer Media Ltd
All times are GMT -5. The time now is 17:40.
Find Us