How to permanently change the colour of the cell using conditional formation VBA?

[Azzariolima 93]

Hello, I would like to change the colour of the cells based on a rule permanently. I used conditional formating for top 2 values in each of my columns. It was supposed to change the colour of top 2 cells in red and it did it, but after that I needed to copy and paste only the colour of the cells not the actual formula behind the colour. When I check the format of the cell, it says that there is no background colour. I need to copy these colours and paste them on another range of sells, ONLY the colours. I asked a friend for a macro and this is what he created for me, but it does the same job as conditional formating:
Sub SortColoredCells()
Dim rng2 As Range
For Each rng2 In ActiveSheet.UsedRange.Columns
c_name = GetColumnLetter(rng2.Cells.Column)
ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.SortFields.Add(Range(c_name & "2:" & c_name & "1000"), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 0, 0)
With ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort
.SetRange Range(c_name & "1:" & c_name & "1000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Next
End Sub

Thank you for you help!

Cross post https://stackoverflow.com/questions...cell-using-conditional-formation-vba/51794674

 

[LFKim2018]

Hello, I would like to change the colour of the cells based on a rule permanently. I used conditional formating for top 2 values in each of my columns. It was supposed to change the colour of top 2 cells in red and it did it, but after that I needed to copy and paste only the colour of the cells not the actual formula behind the colour. When I check the format of the cell, it says that there is no background colour. I need to copy these colours and paste them on another range of sells, ONLY the colours. I asked a friend for a macro and this is what he created for me, but it does the same job as conditional formating:
Sub SortColoredCells()
Dim rng2 As Range
For Each rng2 In ActiveSheet.UsedRange.Columns
c_name = GetColumnLetter(rng2.Cells.Column)
ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort.SortFields.Add(Range(c_name & "2:" & c_name & "1000"), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 0, 0)
With ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort
.SetRange Range(c_name & "1:" & c_name & "1000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Next
End Sub

Thank you for you help!

https://stackoverflow.com/questions/ask?title=

Don't know if I get you right, but couldn't you just copy then paste special - formats or validation only?
HTH

 

[yky]

Conditional formatting doesn't really set the cell color. It sets a color that you can see but it's not the background color. Your friend probably did not understand your need. Ask him to set the background color using this code:

If condition=True then
Range("A1").Interior.ColorIndex=3
End If

Then, you can copy the background color.