Forum Discussion

Tollyver's avatar
Tollyver
Copper Contributor
Jul 08, 2020
Solved

Need help for Conditional Formatting

Hi everyone;   I have a school bus list that contains student's name and Phone numbers. Only one cell contains all these informations for a student. I want to use conditional formatting method for ...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 08, 2020

    Tollyver , Subodh_Tiwari_sktneer 

    I'd modify a bit. If we shall exclude notes let consider as duplicates only cells where first two lines of text are the same. When we may apply conditional formatting rules to entire columns

    with formula

    =(A1<>"")*COUNTIF($A:$B,LEFT(A1,IFERROR(FIND(CHAR(10),A1,FIND(CHAR(10),A1)+1)-1,LEN(A1)))&"*")>1

    or similar if we take another range.

Subodh_Tiwari_sktneer's avatar
Subodh_Tiwari_sktneer
Silver Contributor
Jul 08, 2020

Tollyver 

Try making a New Formula for conditional formatting using the formula given below to do so follow these steps...

 

  1. Select your data range starting from cell A2 e.g. as per your sample data, select the range A2:B6.
  2. Home Tab --> Conditional Formatting --> choose New Rule and in the next window select "Use a formula to determine which cells to format".
  3. In this window itself, in the InputBox which is just below the line which says "format values where this formula is true:", paste the formula given below.
  4. Now click on Format and set the format as per your choice.

Conditional Formatting Formula:

=COUNTIF($A:$B,A2)>1

 

Resources