Applying Conditional Formatting to only the first 10 strings in a cell

[Tennisguuy]

I want to apply color to the first 10 characters in cell B63. It has a formula in the cell. I selected conditional formatting, then New Rule, then Use of formula to determine which cell to format then I entered this formula =LEN($B63)=10. It worked and applied the color to the first 10 characters but that is because it was only 10 characters in the cell. Sometimes the cell characters might be 15 and in that case it will not work but will work if I change the formula to =LEN($B63)>=10 but then it applies it to the entire 15 characters are the number of characters in the cell.

I only want it to apply it to the first 10 characters if the number of characters in cell is greater than 10. I tried this formula but received an error =LEN($B63)>=10,LEN($B63)=10 but I got an error message.

Is there a way to apply conditional formatting to just the first 10 characters.

 

[DanteAmor]

Dante... that won't work because the cells still has formulas in them. If you set each cell equal to its value first, then it would work, but then the formula would be gone. The only way I can think to do this is to replace the formulas with event code that duplicates the action of the formulas for those cells... that way the cells would contain constants which could then have multiple colors applied to them. In order to do that, though, the OP would have to show us the formulas that are in each of those cells (B63, C63, E63, F63, H63, I63, J63, K63).

That's right Rick, the OP said he had a formula in the cell and I skipped that part .
@Tennisguuy, the macro does not work if you have formulas in the cells. If you want it to work you should change the formula for its value.

 

[Tennisguuy]

Guys thanks for your help. Dante the value in the formula is not fixed it will change based on the value the formula is pulling. Therefore it sounds like this will not work which is ok. Again thanks for your help you guys are awesome.

 

[Rick Rothstein]

Therefore it sounds like this will not work which is ok.

Did you want to try my idea of replacing the formulas with event code (you and your users would not notice any functional difference)? If so, show us the formula that is in each of the cells (B63, C63, E63, F63, H63, I63, J63, K63).

 

[Tennisguuy]

Rick, I am not sure why you mean by event code but here are the formulas I have in each of the cells. I am assuming you mean you will put the formulas in the code

B63 IF('Rate Scenario'!H10=0,"Priv. Pass",IF(AND('Rate Scenario'!H10>0,'Rate Scenario'!D58=""),"Priv. Pass","Priv. Pass -"&'Rate Scenario'!D58))
C63 IF('Rate Scenario'!H10=0,"Light Trk",IF(AND('Rate Scenario'!H10>0,'Rate Scenario'!D60=""),"Light Trk","Light Trk -"&'Rate Scenario'!D60))
E63 IF('Rate Scenario'!H10=0,"Medium Trk",IF(AND('Rate Scenario'!H10>0,'Rate Scenario'!D62=""),"Med Trk","Med Trk -"&'Rate Scenario'!D62))
F63 IF('Rate Scenario'!H10=0,"Heavy Trk",IF(AND('Rate Scenario'!H10>0,'Rate Scenario'!D64=""),"Heavy Trk","Heavy Trk -"&'Rate Scenario'!D64))
H63 IF('Rate Scenario'!H10=0,"XHvy Trk",IF(AND('Rate Scenario'!H10>0,'Rate Scenario'!D66=""),"XHvy Trk","XHvy Trk -"&'Rate Scenario'!D66))
I63 IF('Rate Scenario'!H10=0,"Tractor",IF(AND('Rate Scenario'!H10>0,'Rate Scenario'!D68=""),"Tractor","Tractor -"&'Rate Scenario'!D68))
J63 IF('Rate Scenario'!H10=0,"Trailer",IF(AND('Rate Scenario'!H10>0,'Rate Scenario'!D70=""),"Trailer","Trailer -"&'Rate Scenario'!D70))
K63 IF('Rate Scenario'!H10=0,"Total P/U",IF(AND('Rate Scenario'!H10>0,'Rate Scenario'!D72>0),"Total P/U","Total P/U -"&'Rate Scenario'!D72))