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

Tennisguuy

Well-known Member
Joined
Oct 17, 2007
Messages
564
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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.
 
Upvote 0
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.
 
Upvote 0
Ok thanks I thought it was a long shot but figured I would ask just in case. Appreciate your response.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
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.
 
Upvote 0
The offer continues if you want it later. ;)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top