Conditional formatting for restricted part in each cell in EXCEL The Next CEO of Stack OverflowExcel conditional formatting - highlight cells based on a value at the top of each columnExcel: Conditional formatting for clusters of values(Excel) Conditional Formatting based on Adjacent Cell ValueExcel VBA Select Cells based on Conditional Formatting appliedNot able to delete text in an excel cellEquation/ sorting to separate one long column of data into separate columnsExcel conditional formatting per row cellsExcel conditional formatting formula if THIS current cell is blankExcel - Self reference a cell in conditional formatting
Direct Implications Between USA and UK in Event of No-Deal Brexit
Cannot restore registry to default in Windows 10?
Shortening a title without changing its meaning
Why was Sir Cadogan fired?
How to compactly explain secondary and tertiary characters without resorting to stereotypes?
Strange use of "whether ... than ..." in official text
MT "will strike" & LXX "will watch carefully" (Gen 3:15)?
Would a grinding machine be a simple and workable propulsion system for an interplanetary spacecraft?
"Eavesdropping" vs "Listen in on"
Could a dragon use its wings to swim?
Could you use a laser beam as a modulated carrier wave for radio signal?
Is there a rule of thumb for determining the amount one should accept for a settlement offer?
Calculating discount not working
Is it reasonable to ask other researchers to send me their previous grant applications?
Does Germany produce more waste than the US?
Can I cast Thunderwave and be at the center of its bottom face, but not be affected by it?
What is the difference between 'contrib' and 'non-free' packages repositories?
Can you teleport closer to a creature you are Frightened of?
Oldie but Goldie
Simplify trigonometric expression using trigonometric identities
Is the offspring between a demon and a celestial possible? If so what is it called and is it in a book somewhere?
Upgrading From a 9 Speed Sora Derailleur?
How does a dynamic QR code work?
Raspberry pi 3 B with Ubuntu 18.04 server arm64: what pi version
Conditional formatting for restricted part in each cell in EXCEL
The Next CEO of Stack OverflowExcel conditional formatting - highlight cells based on a value at the top of each columnExcel: Conditional formatting for clusters of values(Excel) Conditional Formatting based on Adjacent Cell ValueExcel VBA Select Cells based on Conditional Formatting appliedNot able to delete text in an excel cellEquation/ sorting to separate one long column of data into separate columnsExcel conditional formatting per row cellsExcel conditional formatting formula if THIS current cell is blankExcel - Self reference a cell in conditional formatting
Suppose we have 3 cells, with content respectively "123 (45)", "456 (67)" and "789 (89)". Is it possible to formatting the three cells based on only their first values, i.e., "123", "456", "789" ?
To make it clear:
How to still format as shown in the picture suppose I have "(XX)" following each number in each cell,
i.e., 480 (XX), 7 (XX), 112 (XX)''''''''
excel
|
show 2 more comments
Suppose we have 3 cells, with content respectively "123 (45)", "456 (67)" and "789 (89)". Is it possible to formatting the three cells based on only their first values, i.e., "123", "456", "789" ?
To make it clear:
How to still format as shown in the picture suppose I have "(XX)" following each number in each cell,
i.e., 480 (XX), 7 (XX), 112 (XX)''''''''
excel
Yes. (How? Conditional Formatting has a formula-based option. Determine the Formula required to extract the section of the values you're interested in, convert to numbers if required, set formatting. Repeat for each specific format desired.) Depending on how specific an answer you're looking for, you may need to add more information about what type of conditional formatting you're looking for.
– Mistella
Mar 8 at 19:30
Thanks, my goal is to format the cells based on the values of the restricted parts.
– Fumin Wang
Mar 8 at 21:08
it can be done with vba if you need an example i will update my answer for you
– Erjon
Mar 8 at 21:39
the problem is that you have to put =NUMBERVALUE(LEFT(A1:A10,3)) into an array, and i don't think data bar is able to recognize arrays
– Erjon
Mar 8 at 21:52
or how about to extract in a hidden column only the values and then apply databars
– Erjon
Mar 8 at 21:57
|
show 2 more comments
Suppose we have 3 cells, with content respectively "123 (45)", "456 (67)" and "789 (89)". Is it possible to formatting the three cells based on only their first values, i.e., "123", "456", "789" ?
To make it clear:
How to still format as shown in the picture suppose I have "(XX)" following each number in each cell,
i.e., 480 (XX), 7 (XX), 112 (XX)''''''''
excel
Suppose we have 3 cells, with content respectively "123 (45)", "456 (67)" and "789 (89)". Is it possible to formatting the three cells based on only their first values, i.e., "123", "456", "789" ?
To make it clear:
How to still format as shown in the picture suppose I have "(XX)" following each number in each cell,
i.e., 480 (XX), 7 (XX), 112 (XX)''''''''
excel
excel
edited Mar 8 at 21:30
Fumin Wang
asked Mar 8 at 19:21
Fumin WangFumin Wang
254
254
Yes. (How? Conditional Formatting has a formula-based option. Determine the Formula required to extract the section of the values you're interested in, convert to numbers if required, set formatting. Repeat for each specific format desired.) Depending on how specific an answer you're looking for, you may need to add more information about what type of conditional formatting you're looking for.
– Mistella
Mar 8 at 19:30
Thanks, my goal is to format the cells based on the values of the restricted parts.
– Fumin Wang
Mar 8 at 21:08
it can be done with vba if you need an example i will update my answer for you
– Erjon
Mar 8 at 21:39
the problem is that you have to put =NUMBERVALUE(LEFT(A1:A10,3)) into an array, and i don't think data bar is able to recognize arrays
– Erjon
Mar 8 at 21:52
or how about to extract in a hidden column only the values and then apply databars
– Erjon
Mar 8 at 21:57
|
show 2 more comments
Yes. (How? Conditional Formatting has a formula-based option. Determine the Formula required to extract the section of the values you're interested in, convert to numbers if required, set formatting. Repeat for each specific format desired.) Depending on how specific an answer you're looking for, you may need to add more information about what type of conditional formatting you're looking for.
– Mistella
Mar 8 at 19:30
Thanks, my goal is to format the cells based on the values of the restricted parts.
– Fumin Wang
Mar 8 at 21:08
it can be done with vba if you need an example i will update my answer for you
– Erjon
Mar 8 at 21:39
the problem is that you have to put =NUMBERVALUE(LEFT(A1:A10,3)) into an array, and i don't think data bar is able to recognize arrays
– Erjon
Mar 8 at 21:52
or how about to extract in a hidden column only the values and then apply databars
– Erjon
Mar 8 at 21:57
Yes. (How? Conditional Formatting has a formula-based option. Determine the Formula required to extract the section of the values you're interested in, convert to numbers if required, set formatting. Repeat for each specific format desired.) Depending on how specific an answer you're looking for, you may need to add more information about what type of conditional formatting you're looking for.
– Mistella
Mar 8 at 19:30
Yes. (How? Conditional Formatting has a formula-based option. Determine the Formula required to extract the section of the values you're interested in, convert to numbers if required, set formatting. Repeat for each specific format desired.) Depending on how specific an answer you're looking for, you may need to add more information about what type of conditional formatting you're looking for.
– Mistella
Mar 8 at 19:30
Thanks, my goal is to format the cells based on the values of the restricted parts.
– Fumin Wang
Mar 8 at 21:08
Thanks, my goal is to format the cells based on the values of the restricted parts.
– Fumin Wang
Mar 8 at 21:08
it can be done with vba if you need an example i will update my answer for you
– Erjon
Mar 8 at 21:39
it can be done with vba if you need an example i will update my answer for you
– Erjon
Mar 8 at 21:39
the problem is that you have to put =NUMBERVALUE(LEFT(A1:A10,3)) into an array, and i don't think data bar is able to recognize arrays
– Erjon
Mar 8 at 21:52
the problem is that you have to put =NUMBERVALUE(LEFT(A1:A10,3)) into an array, and i don't think data bar is able to recognize arrays
– Erjon
Mar 8 at 21:52
or how about to extract in a hidden column only the values and then apply databars
– Erjon
Mar 8 at 21:57
or how about to extract in a hidden column only the values and then apply databars
– Erjon
Mar 8 at 21:57
|
show 2 more comments
1 Answer
1
active
oldest
votes
if the cell contain the same pattern for example: 123 (45)
then you can use Conditional Formatting the formula-based option and put this formula:
=NUMBERVALUE(LEFT(A1,3)) = 123
or
=NUMBERVALUE(LEFT(A1,3))=B1
B1 if you want to reference to the value of some cell
and do the desired formatting
if the pattern is not the same but for example some are like: 123 (45), 1234 (46) then:
=NUMBERVALUE(LEFT(A1,FIND(" ",A1)-1)) = 123
or
=NUMBERVALUE(LEFT(A1,FIND(" ",A1)-1)) =B1
EDIT
The easy way of doing that is:
In another column next to the column which has tha data put this formula:
=NUMBERVALUE(LEFT(A1,FIND(" ",A1)-1))
then you can apply data bars conditional formatting, inside the conditional formatting options for data bar there is a check box "show bar only" and the result is going to be something like this:
Another way is in another column put the formula:
=A1&" "&REPT("|",NUMBERVALUE(LEFT(A1,FIND(" ",A1)-1))/5)
or this
=A8&" "&REPT("∙",NUMBERVALUE(LEFT(A8,FIND(" ",A8)-1))/5)
there is no other easy way of doing that cause data bar conditional formatting does not accept array formula.
You can do it anyway with a macro in the sheet which has the data copy and paste the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long
Dim data As Range
Set data = ActiveSheet.Range("A:A")
If Not Intersect(Target, data) Is Nothing Then
lRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
Dim arr() As Double
On Error Resume Next
For i = 1 To lRow
Set myrange = ActiveSheet.Range("A" & i)
ReDim Preserve arr(i)
arr(i) = Left(myrange, WorksheetFunction.Find(" ", myrange) - 1)
Next i
Dim MaxValue As Long
MaxValue = WorksheetFunction.Max(arr)
For i = 1 To lRow
Set myrange = ActiveSheet.Range("A" & i)
With myrange.Interior
.Pattern = xlPatternLinearGradient
.Gradient.Degree = 180
.Gradient.ColorStops.Clear
End With
With myrange.Interior.Gradient.ColorStops.Add(1)
.Color = RGB(13, 71, 161)
.TintAndShade = 0
End With
With myrange.Interior.Gradient.ColorStops.Add(1 - (arr(i) / MaxValue))
.Color = RGB(13, 71, 161)
.TintAndShade = 1
End With
With myrange.Interior.Gradient.ColorStops.Add(0)
.Color = RGB(255, 255, 255)
.TintAndShade = 1
End With
Next i
End If
End Sub
So with this code every time you put a new data in the column it will automatically format it like the image below. I have tested and it works just paste it in your sheet, not in a module and change the ranges as fits to you.
Hope it helps!
in what sense stripes, can you provide a photo of what you want to do?
– Erjon
Mar 8 at 21:08
Well, appreciate it. My goal is to format the cells based on the values of my interested part. In this case, 456 (XX) should have a longer strip displayed than 123(XX)
– Fumin Wang
Mar 8 at 21:11
Yes, I have this question updated, thank you.
– Fumin Wang
Mar 8 at 21:31
@FuminWang did you checked my last answer, the vba code will do the requested task for you, check it and tell me if it helped.
– Erjon
Mar 12 at 22:43
That's helpful, let me take time to digest all of them, thank you !
– Fumin Wang
Mar 13 at 13:42
|
show 1 more comment
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55069668%2fconditional-formatting-for-restricted-part-in-each-cell-in-excel%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
if the cell contain the same pattern for example: 123 (45)
then you can use Conditional Formatting the formula-based option and put this formula:
=NUMBERVALUE(LEFT(A1,3)) = 123
or
=NUMBERVALUE(LEFT(A1,3))=B1
B1 if you want to reference to the value of some cell
and do the desired formatting
if the pattern is not the same but for example some are like: 123 (45), 1234 (46) then:
=NUMBERVALUE(LEFT(A1,FIND(" ",A1)-1)) = 123
or
=NUMBERVALUE(LEFT(A1,FIND(" ",A1)-1)) =B1
EDIT
The easy way of doing that is:
In another column next to the column which has tha data put this formula:
=NUMBERVALUE(LEFT(A1,FIND(" ",A1)-1))
then you can apply data bars conditional formatting, inside the conditional formatting options for data bar there is a check box "show bar only" and the result is going to be something like this:
Another way is in another column put the formula:
=A1&" "&REPT("|",NUMBERVALUE(LEFT(A1,FIND(" ",A1)-1))/5)
or this
=A8&" "&REPT("∙",NUMBERVALUE(LEFT(A8,FIND(" ",A8)-1))/5)
there is no other easy way of doing that cause data bar conditional formatting does not accept array formula.
You can do it anyway with a macro in the sheet which has the data copy and paste the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long
Dim data As Range
Set data = ActiveSheet.Range("A:A")
If Not Intersect(Target, data) Is Nothing Then
lRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
Dim arr() As Double
On Error Resume Next
For i = 1 To lRow
Set myrange = ActiveSheet.Range("A" & i)
ReDim Preserve arr(i)
arr(i) = Left(myrange, WorksheetFunction.Find(" ", myrange) - 1)
Next i
Dim MaxValue As Long
MaxValue = WorksheetFunction.Max(arr)
For i = 1 To lRow
Set myrange = ActiveSheet.Range("A" & i)
With myrange.Interior
.Pattern = xlPatternLinearGradient
.Gradient.Degree = 180
.Gradient.ColorStops.Clear
End With
With myrange.Interior.Gradient.ColorStops.Add(1)
.Color = RGB(13, 71, 161)
.TintAndShade = 0
End With
With myrange.Interior.Gradient.ColorStops.Add(1 - (arr(i) / MaxValue))
.Color = RGB(13, 71, 161)
.TintAndShade = 1
End With
With myrange.Interior.Gradient.ColorStops.Add(0)
.Color = RGB(255, 255, 255)
.TintAndShade = 1
End With
Next i
End If
End Sub
So with this code every time you put a new data in the column it will automatically format it like the image below. I have tested and it works just paste it in your sheet, not in a module and change the ranges as fits to you.
Hope it helps!
in what sense stripes, can you provide a photo of what you want to do?
– Erjon
Mar 8 at 21:08
Well, appreciate it. My goal is to format the cells based on the values of my interested part. In this case, 456 (XX) should have a longer strip displayed than 123(XX)
– Fumin Wang
Mar 8 at 21:11
Yes, I have this question updated, thank you.
– Fumin Wang
Mar 8 at 21:31
@FuminWang did you checked my last answer, the vba code will do the requested task for you, check it and tell me if it helped.
– Erjon
Mar 12 at 22:43
That's helpful, let me take time to digest all of them, thank you !
– Fumin Wang
Mar 13 at 13:42
|
show 1 more comment
if the cell contain the same pattern for example: 123 (45)
then you can use Conditional Formatting the formula-based option and put this formula:
=NUMBERVALUE(LEFT(A1,3)) = 123
or
=NUMBERVALUE(LEFT(A1,3))=B1
B1 if you want to reference to the value of some cell
and do the desired formatting
if the pattern is not the same but for example some are like: 123 (45), 1234 (46) then:
=NUMBERVALUE(LEFT(A1,FIND(" ",A1)-1)) = 123
or
=NUMBERVALUE(LEFT(A1,FIND(" ",A1)-1)) =B1
EDIT
The easy way of doing that is:
In another column next to the column which has tha data put this formula:
=NUMBERVALUE(LEFT(A1,FIND(" ",A1)-1))
then you can apply data bars conditional formatting, inside the conditional formatting options for data bar there is a check box "show bar only" and the result is going to be something like this:
Another way is in another column put the formula:
=A1&" "&REPT("|",NUMBERVALUE(LEFT(A1,FIND(" ",A1)-1))/5)
or this
=A8&" "&REPT("∙",NUMBERVALUE(LEFT(A8,FIND(" ",A8)-1))/5)
there is no other easy way of doing that cause data bar conditional formatting does not accept array formula.
You can do it anyway with a macro in the sheet which has the data copy and paste the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long
Dim data As Range
Set data = ActiveSheet.Range("A:A")
If Not Intersect(Target, data) Is Nothing Then
lRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
Dim arr() As Double
On Error Resume Next
For i = 1 To lRow
Set myrange = ActiveSheet.Range("A" & i)
ReDim Preserve arr(i)
arr(i) = Left(myrange, WorksheetFunction.Find(" ", myrange) - 1)
Next i
Dim MaxValue As Long
MaxValue = WorksheetFunction.Max(arr)
For i = 1 To lRow
Set myrange = ActiveSheet.Range("A" & i)
With myrange.Interior
.Pattern = xlPatternLinearGradient
.Gradient.Degree = 180
.Gradient.ColorStops.Clear
End With
With myrange.Interior.Gradient.ColorStops.Add(1)
.Color = RGB(13, 71, 161)
.TintAndShade = 0
End With
With myrange.Interior.Gradient.ColorStops.Add(1 - (arr(i) / MaxValue))
.Color = RGB(13, 71, 161)
.TintAndShade = 1
End With
With myrange.Interior.Gradient.ColorStops.Add(0)
.Color = RGB(255, 255, 255)
.TintAndShade = 1
End With
Next i
End If
End Sub
So with this code every time you put a new data in the column it will automatically format it like the image below. I have tested and it works just paste it in your sheet, not in a module and change the ranges as fits to you.
Hope it helps!
in what sense stripes, can you provide a photo of what you want to do?
– Erjon
Mar 8 at 21:08
Well, appreciate it. My goal is to format the cells based on the values of my interested part. In this case, 456 (XX) should have a longer strip displayed than 123(XX)
– Fumin Wang
Mar 8 at 21:11
Yes, I have this question updated, thank you.
– Fumin Wang
Mar 8 at 21:31
@FuminWang did you checked my last answer, the vba code will do the requested task for you, check it and tell me if it helped.
– Erjon
Mar 12 at 22:43
That's helpful, let me take time to digest all of them, thank you !
– Fumin Wang
Mar 13 at 13:42
|
show 1 more comment
if the cell contain the same pattern for example: 123 (45)
then you can use Conditional Formatting the formula-based option and put this formula:
=NUMBERVALUE(LEFT(A1,3)) = 123
or
=NUMBERVALUE(LEFT(A1,3))=B1
B1 if you want to reference to the value of some cell
and do the desired formatting
if the pattern is not the same but for example some are like: 123 (45), 1234 (46) then:
=NUMBERVALUE(LEFT(A1,FIND(" ",A1)-1)) = 123
or
=NUMBERVALUE(LEFT(A1,FIND(" ",A1)-1)) =B1
EDIT
The easy way of doing that is:
In another column next to the column which has tha data put this formula:
=NUMBERVALUE(LEFT(A1,FIND(" ",A1)-1))
then you can apply data bars conditional formatting, inside the conditional formatting options for data bar there is a check box "show bar only" and the result is going to be something like this:
Another way is in another column put the formula:
=A1&" "&REPT("|",NUMBERVALUE(LEFT(A1,FIND(" ",A1)-1))/5)
or this
=A8&" "&REPT("∙",NUMBERVALUE(LEFT(A8,FIND(" ",A8)-1))/5)
there is no other easy way of doing that cause data bar conditional formatting does not accept array formula.
You can do it anyway with a macro in the sheet which has the data copy and paste the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long
Dim data As Range
Set data = ActiveSheet.Range("A:A")
If Not Intersect(Target, data) Is Nothing Then
lRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
Dim arr() As Double
On Error Resume Next
For i = 1 To lRow
Set myrange = ActiveSheet.Range("A" & i)
ReDim Preserve arr(i)
arr(i) = Left(myrange, WorksheetFunction.Find(" ", myrange) - 1)
Next i
Dim MaxValue As Long
MaxValue = WorksheetFunction.Max(arr)
For i = 1 To lRow
Set myrange = ActiveSheet.Range("A" & i)
With myrange.Interior
.Pattern = xlPatternLinearGradient
.Gradient.Degree = 180
.Gradient.ColorStops.Clear
End With
With myrange.Interior.Gradient.ColorStops.Add(1)
.Color = RGB(13, 71, 161)
.TintAndShade = 0
End With
With myrange.Interior.Gradient.ColorStops.Add(1 - (arr(i) / MaxValue))
.Color = RGB(13, 71, 161)
.TintAndShade = 1
End With
With myrange.Interior.Gradient.ColorStops.Add(0)
.Color = RGB(255, 255, 255)
.TintAndShade = 1
End With
Next i
End If
End Sub
So with this code every time you put a new data in the column it will automatically format it like the image below. I have tested and it works just paste it in your sheet, not in a module and change the ranges as fits to you.
Hope it helps!
if the cell contain the same pattern for example: 123 (45)
then you can use Conditional Formatting the formula-based option and put this formula:
=NUMBERVALUE(LEFT(A1,3)) = 123
or
=NUMBERVALUE(LEFT(A1,3))=B1
B1 if you want to reference to the value of some cell
and do the desired formatting
if the pattern is not the same but for example some are like: 123 (45), 1234 (46) then:
=NUMBERVALUE(LEFT(A1,FIND(" ",A1)-1)) = 123
or
=NUMBERVALUE(LEFT(A1,FIND(" ",A1)-1)) =B1
EDIT
The easy way of doing that is:
In another column next to the column which has tha data put this formula:
=NUMBERVALUE(LEFT(A1,FIND(" ",A1)-1))
then you can apply data bars conditional formatting, inside the conditional formatting options for data bar there is a check box "show bar only" and the result is going to be something like this:
Another way is in another column put the formula:
=A1&" "&REPT("|",NUMBERVALUE(LEFT(A1,FIND(" ",A1)-1))/5)
or this
=A8&" "&REPT("∙",NUMBERVALUE(LEFT(A8,FIND(" ",A8)-1))/5)
there is no other easy way of doing that cause data bar conditional formatting does not accept array formula.
You can do it anyway with a macro in the sheet which has the data copy and paste the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long
Dim data As Range
Set data = ActiveSheet.Range("A:A")
If Not Intersect(Target, data) Is Nothing Then
lRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
Dim arr() As Double
On Error Resume Next
For i = 1 To lRow
Set myrange = ActiveSheet.Range("A" & i)
ReDim Preserve arr(i)
arr(i) = Left(myrange, WorksheetFunction.Find(" ", myrange) - 1)
Next i
Dim MaxValue As Long
MaxValue = WorksheetFunction.Max(arr)
For i = 1 To lRow
Set myrange = ActiveSheet.Range("A" & i)
With myrange.Interior
.Pattern = xlPatternLinearGradient
.Gradient.Degree = 180
.Gradient.ColorStops.Clear
End With
With myrange.Interior.Gradient.ColorStops.Add(1)
.Color = RGB(13, 71, 161)
.TintAndShade = 0
End With
With myrange.Interior.Gradient.ColorStops.Add(1 - (arr(i) / MaxValue))
.Color = RGB(13, 71, 161)
.TintAndShade = 1
End With
With myrange.Interior.Gradient.ColorStops.Add(0)
.Color = RGB(255, 255, 255)
.TintAndShade = 1
End With
Next i
End If
End Sub
So with this code every time you put a new data in the column it will automatically format it like the image below. I have tested and it works just paste it in your sheet, not in a module and change the ranges as fits to you.
Hope it helps!
edited Mar 12 at 22:42
answered Mar 8 at 19:40
ErjonErjon
13410
13410
in what sense stripes, can you provide a photo of what you want to do?
– Erjon
Mar 8 at 21:08
Well, appreciate it. My goal is to format the cells based on the values of my interested part. In this case, 456 (XX) should have a longer strip displayed than 123(XX)
– Fumin Wang
Mar 8 at 21:11
Yes, I have this question updated, thank you.
– Fumin Wang
Mar 8 at 21:31
@FuminWang did you checked my last answer, the vba code will do the requested task for you, check it and tell me if it helped.
– Erjon
Mar 12 at 22:43
That's helpful, let me take time to digest all of them, thank you !
– Fumin Wang
Mar 13 at 13:42
|
show 1 more comment
in what sense stripes, can you provide a photo of what you want to do?
– Erjon
Mar 8 at 21:08
Well, appreciate it. My goal is to format the cells based on the values of my interested part. In this case, 456 (XX) should have a longer strip displayed than 123(XX)
– Fumin Wang
Mar 8 at 21:11
Yes, I have this question updated, thank you.
– Fumin Wang
Mar 8 at 21:31
@FuminWang did you checked my last answer, the vba code will do the requested task for you, check it and tell me if it helped.
– Erjon
Mar 12 at 22:43
That's helpful, let me take time to digest all of them, thank you !
– Fumin Wang
Mar 13 at 13:42
in what sense stripes, can you provide a photo of what you want to do?
– Erjon
Mar 8 at 21:08
in what sense stripes, can you provide a photo of what you want to do?
– Erjon
Mar 8 at 21:08
Well, appreciate it. My goal is to format the cells based on the values of my interested part. In this case, 456 (XX) should have a longer strip displayed than 123(XX)
– Fumin Wang
Mar 8 at 21:11
Well, appreciate it. My goal is to format the cells based on the values of my interested part. In this case, 456 (XX) should have a longer strip displayed than 123(XX)
– Fumin Wang
Mar 8 at 21:11
Yes, I have this question updated, thank you.
– Fumin Wang
Mar 8 at 21:31
Yes, I have this question updated, thank you.
– Fumin Wang
Mar 8 at 21:31
@FuminWang did you checked my last answer, the vba code will do the requested task for you, check it and tell me if it helped.
– Erjon
Mar 12 at 22:43
@FuminWang did you checked my last answer, the vba code will do the requested task for you, check it and tell me if it helped.
– Erjon
Mar 12 at 22:43
That's helpful, let me take time to digest all of them, thank you !
– Fumin Wang
Mar 13 at 13:42
That's helpful, let me take time to digest all of them, thank you !
– Fumin Wang
Mar 13 at 13:42
|
show 1 more comment
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55069668%2fconditional-formatting-for-restricted-part-in-each-cell-in-excel%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Yes. (How? Conditional Formatting has a formula-based option. Determine the Formula required to extract the section of the values you're interested in, convert to numbers if required, set formatting. Repeat for each specific format desired.) Depending on how specific an answer you're looking for, you may need to add more information about what type of conditional formatting you're looking for.
– Mistella
Mar 8 at 19:30
Thanks, my goal is to format the cells based on the values of the restricted parts.
– Fumin Wang
Mar 8 at 21:08
it can be done with vba if you need an example i will update my answer for you
– Erjon
Mar 8 at 21:39
the problem is that you have to put =NUMBERVALUE(LEFT(A1:A10,3)) into an array, and i don't think data bar is able to recognize arrays
– Erjon
Mar 8 at 21:52
or how about to extract in a hidden column only the values and then apply databars
– Erjon
Mar 8 at 21:57