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.

 

[Fluff]

You cannot partially format a cell that contains a formula.
If the cell had a hard value, then it would be possible, but it would need VBA.

 

[DanteAmor]

Sorry, conditional formatting can only be applied to the entire cell contents.

If you want to highlight part of a text it should be with a macro.
If you want the macro you must explain in which cell range to apply the macro and if it should apply some condition or it is for all the cells in your range.

 

[Tennisguuy]

Ok thanks I thought it was a long shot but figured I would ask just in case. Appreciate your response.

 

[Fluff]

You're welcome & thanks for the feedback

 

[Tennisguuy]

Donte thanks for the offer to create the macro. I really appreciate it but there were other cells that I wanted it to apply but the string length is different in each case that I would want the condition to apply. It was just something that would make it standout if it was an easy fix but it isn't a must but again really appreciate the offer to help. You guys are great.

 

[DanteAmor]

The offer continues if you want it later.

 

[Tennisguuy]

Ok if it doesn't take too long. The sheet name is Notes. The cell range is B63:K63.

For cell B63 I want the first 10 characters to color Blue
For cell C63 I want the first 9 characters to color Red
For cell E63 I want the first 10 characters to color Green
For cell F63 I want the first 9 characters to color Blue
For cell H63 I want the first 8 characters to color Red
For cell I63 I want the first 7 characters to color Green
For cell J63 I want the first 7 characters to color Blue
For cell K63 I want the first 9 characters to color Red

If it's easier you can make them all the same color of Blue. Let me know if you need anything else.

 

[DanteAmor]

Run this macro. You can add or remove cells, but you must do it in all 3 arrays.

Sub applyColor()
  Dim aCells(), aChars(), aColor(), c As Long
  aCells = Array("B63", "C63", "E63", "F63", "H63", "I63", "J63", "K63")
  aChars = Array(10, 9, 10, 9, 8, 7, 7, 9)
  aColor = Array(vbBlue, vbRed, vbGreen, vbBlue, vbRed, vbGreen, vbBlue, vbRed)
  For c = 0 To UBound(aCells)
    Range(aCells(c)).Characters(Start:=1, Length:=aChars(c)).Font.Color = aColor(c)
  Next
End Sub

 

[Rick Rothstein]

Run this macro. You can add or remove cells, but you must do it in all 3 arrays.

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).