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

Tennisguuy

Well-known Member
Joined
Oct 17, 2007
Messages
541
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,732
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
9,684
Office Version
2007
Platform
Windows
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

Well-known Member
Joined
Oct 17, 2007
Messages
541
Ok thanks I thought it was a long shot but figured I would ask just in case. Appreciate your response.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,732
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Tennisguuy

Well-known Member
Joined
Oct 17, 2007
Messages
541
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

Well-known Member
Joined
Dec 3, 2018
Messages
9,684
Office Version
2007
Platform
Windows
The offer continues if you want it later. ;)
 

Tennisguuy

Well-known Member
Joined
Oct 17, 2007
Messages
541
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

Well-known Member
Joined
Dec 3, 2018
Messages
9,684
Office Version
2007
Platform
Windows
Run this macro. You can add or remove cells, but you must do it in all 3 arrays.

Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,571
Office Version
2010
Platform
Windows
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).
 
Last edited:

Forum statistics

Threads
1,085,545
Messages
5,384,371
Members
401,890
Latest member
Angela7

Some videos you may like

This Week's Hot Topics

Top