Conditional formatting to multiple cells in single column M

[kishore k]

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]

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

 

[kishore k]

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]

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