Conditional formatting to multiple cells in single column M

kishore k

New Member
Joined
Jan 18, 2019
Messages
14
Hi,
I have data in Column A,
A1 Column heading,
A2:A10 - data,
row A11 is blank
A12:A16 -data,
row A17 is blank & so on..
Data in A2:A10 will be like this -
A2 - "J554-Sales1"
A3 - "J554-Sales2"
A4 - "J456-Export1" etc,.
Now, I want a conditional format in entire column A:A, where first four Characters in each cell A2:A4 in which the data is present not equal to "J554", the cell has to turn red color

<colgroup><col></colgroup><tbody>
</tbody>
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,383
make a conditional format formula
for 2007, 2010 , 2013 or 2016 excel version
Conditional Formatting


Highlight applicable range >>

Select the column
A:A


Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:

=AND(LEFT(A1,4)<>"J554", A1<>"")

Format [Number, Font, Border, Fill] choose a fill colour of RED
choose the format you would like to apply when the condition is true
OK >> OK
 
Last edited:

kishore k

New Member
Joined
Jan 18, 2019
Messages
14
Thank you, that works.. but when i change first four characters to check the formula in A2, the red color is appearing in A1 cell, the same case with other cell. Red color is not reflecting in same cell..
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,383
dont check A2 - as you have selected the A column, you need to use A1
Otherwise , you are 1 row out
if you want to use A2 , then you need to select the range
A2:A1000
or to whatever range row you want
 

Forum statistics

Threads
1,078,227
Messages
5,338,965
Members
399,272
Latest member
jakepenner

Some videos you may like

This Week's Hot Topics

Top