You solution still works Chester! Once you have done it for 10 odd cells, just highlight them all, copy, and then paste special and only paste formatting. You can then rinse and repeat until you have 100 cells formatted, and then repeat again etc. Brilliant idea on the OFFSET function though, exactly what I needed.
If you have pasted a range of the format using the format painter, e.g., 10 cells, try to select those 10 cells and double click on the format painter and paste. This will allow you to format 10 cells at a time. It can be repeated using a larger range and so forth. Hope that is understandable.
that's great... I was using index function but was unable to copy formula to all cells. Double click format painter and paste it each row individually.... that's what I needed... Thanks!!!
After doing doubleclick the format painter icon, you are selecting each cell to paste the format. instead you can use drop down key very simply continuously till end and it paste the format as expected. great video though. Appreciate the video.
Thanks, worked for my use case. When I copy and paste the result into powerpoint or outlook I loose the arrows unless I paste as picture (or embedded). Any trick to avoid having to paste as picture?
I found a solution that worked for me without having to individually paste the formatting by using this formula: =INDIRECT(ADDRESS(ROW(),COLUMN()-1)) Hope this helps anyone.
Really clever to add another column and doing (final - initial) to find a negative or positive result to obtain an increase or decrease. It seems like a pain to include another column, but since there is not another better solution (yet), this will do. Thanks for the idea.
Hi Chester, thanks for the video. I was searching a bit on web and found an idea which I extended a bit into the following macro... this should help to overcome the troubles you observed and automate the solution. I have reworked a different macro (I found on web) used for a different purpose but a logic works ok. Take care! Sub CondFormat() Dim rg As Range Dim iset As IconSetCondition Dim LastRow1 As Integer Dim i As Integer LastRow1 = Sheet2.Cells(Sheet2.Rows.Count, "I").End(xlUp).Row For i = 10 To LastRow1
Range("E4").Name = "ProCent" Sheet2.Cells(i, 4).Name = "Diff" & i
Set rg = Sheet2.Cells(i, 9) rg.FormatConditions.Delete Set iset = rg.FormatConditions.AddIconSetCondition With iset .IconSet = ActiveWorkbook.iconsets(xl3Symbols) .ReverseOrder = True .ShowIconOnly = False End With With iset.IconCriteria(2) .Type = xlConditionValueFormula .Operator = xlGreaterEqual .Value = "=(1+max(ProCent-0.2,0))*Diff" & i End With With iset.IconCriteria(3) .Type = xlConditionValueFormula .Operator = xlGreaterEqual .Value = "=(1+ProCent)*Diff" & i End With Next i End Sub
How can you use the ''equal to'' preset conditional formatting in excel to format multiple values in a range at once and not one at a time? Or if there is a better alternative to this conditional formatting, what is it, please?
I'm starter in learning Excel, When I use large values and do conditional formatting or Merge text, values get changed to #### this symbol. Is there any way to work on with.
if i have a condition like that "Use icon sets again to display a green dot for each member of the loading crew (i.e. if the loading crew is set to 5, five dots should be green and one should be black). Also, complete the constraints box. For each constraint, display the word “Met” if the constraint is met and “Not Met” if the constraint is not met. Use the same icons from the model page to indicate whether the constraint is met. Also, turn the text red and bold if it says “Not Met.” "what i should do??
Nicely done, I need one help from you, can you please help me I have similar condition, not exact Please let me know so that I can send you my sheet Thanks, Yuvraj
I want to shuffle the number of 2 colums into 3rd but in different place for Example col1 = 1 2 3 4 5 col2 = 6 7 8 9 10 col3 = 5 8 1 9 2 10 6 4 3 7, Please reply
You solution still works Chester! Once you have done it for 10 odd cells, just highlight them all, copy, and then paste special and only paste formatting. You can then rinse and repeat until you have 100 cells formatted, and then repeat again etc.
Brilliant idea on the OFFSET function though, exactly what I needed.
Thank you very much Chester, I was struggling and so stressed but this helped me a lot
If you have pasted a range of the format using the format painter, e.g., 10 cells, try to select those 10 cells and double click on the format painter and paste. This will allow you to format 10 cells at a time. It can be repeated using a larger range and so forth. Hope that is understandable.
This actually works for me. Thank you for this kind of shortcut, it is very useful.
that's great... I was using index function but was unable to copy formula to all cells. Double click format painter and paste it each row individually.... that's what I needed... Thanks!!!
After doing doubleclick the format painter icon, you are selecting each cell to paste the format. instead you can use drop down key very simply continuously till end and it paste the format as expected. great video though. Appreciate the video.
Thank you very smart sollution!
Brilliant!! I have been searching the exact same thing.!!
Glad it helped!
Thank you. Excellent walkaround!
Very useful, thank you.
Glad it was helpful!
Thank you so much Chester 🙂
Also in the offset formula if you don't keep the reference cell as absolute, you can simply double the conditional formatting to apply on all cells.
Ugh 2 hours later.... Thank you - Thank you so much!
Nicely done, helpful.
VERY VERY HELP FULL. THANK YOU
Brilliant! Just what I was looking for - thanks.
very helpful! thanks
Thanks, worked for my use case. When I copy and paste the result into powerpoint or outlook I loose the arrows unless I paste as picture (or embedded). Any trick to avoid having to paste as picture?
Great...been strugling with for sometime.
I found a solution that worked for me without having to individually paste the formatting by using this formula:
=INDIRECT(ADDRESS(ROW(),COLUMN()-1))
Hope this helps anyone.
I try but is not working, Can u teach me ?
brilliant! Love the workaround.
nice thx, it helps me alot
Would this work for 3 or more columns?
For example: If I want to compare column A with B, B with C, C with D and so on? Appreciate your help.
Really clever to add another column and doing (final - initial) to find a negative or positive result to obtain an increase or decrease. It seems like a pain to include another column, but since there is not another better solution (yet), this will do. Thanks for the idea.
Hi Chester, thanks for the video. I was searching a bit on web and found an idea which I extended a bit into the following macro... this should help to overcome the troubles you observed and automate the solution. I have reworked a different macro (I found on web) used for a different purpose but a logic works ok.
Take care!
Sub CondFormat()
Dim rg As Range
Dim iset As IconSetCondition
Dim LastRow1 As Integer
Dim i As Integer
LastRow1 = Sheet2.Cells(Sheet2.Rows.Count, "I").End(xlUp).Row
For i = 10 To LastRow1
Range("E4").Name = "ProCent"
Sheet2.Cells(i, 4).Name = "Diff" & i
Set rg = Sheet2.Cells(i, 9)
rg.FormatConditions.Delete
Set iset = rg.FormatConditions.AddIconSetCondition
With iset
.IconSet = ActiveWorkbook.iconsets(xl3Symbols)
.ReverseOrder = True
.ShowIconOnly = False
End With
With iset.IconCriteria(2)
.Type = xlConditionValueFormula
.Operator = xlGreaterEqual
.Value = "=(1+max(ProCent-0.2,0))*Diff" & i
End With
With iset.IconCriteria(3)
.Type = xlConditionValueFormula
.Operator = xlGreaterEqual
.Value = "=(1+ProCent)*Diff" & i
End With
Next i
End Sub
How can you use the ''equal to'' preset conditional formatting in excel to format multiple values in a range at once and not one at a time? Or if there is a better alternative to this conditional formatting, what is it, please?
Thank you very helpful
very very helpful! thanks very much.
HI Chester, Did you find a better way since you uploaded the video?
I'm starter in learning Excel, When I use large values and do conditional formatting or Merge text, values get changed to #### this symbol. Is there any way to work on with.
Worked Perfectly:)
Thank you, helps me a lot! :)
if i have a condition like that "Use icon sets again to display a green dot for each member of the loading crew (i.e. if the loading crew is set to 5, five dots should be green and one should be black). Also, complete the constraints box. For each constraint, display the word “Met” if the constraint is met and “Not Met” if the constraint is not met. Use the same icons from the model page to indicate whether the constraint is met. Also, turn the text red and bold if it says “Not Met.” "what i should do??
Thank you so much
Quicker than what I was going to do which was manually format each cell
For 100 rows 😢
Need one to compare dates. If one date is greater than 30 days etc or in between
Nicely done,
I need one help from you, can you please help me
I have similar condition, not exact
Please let me know so that I can send you my sheet
Thanks,
Yuvraj
I want to shuffle the number of 2 colums into 3rd but in different place
for Example
col1 = 1 2 3 4 5
col2 = 6 7 8 9 10
col3 = 5 8 1 9 2 10 6 4 3 7,
Please reply
Good
any solution for 365? because i try and it didnt' work.
+1 :)
Thanks a lot. This was helpful.
thank you very helpful