Input "YES / NO" to a cell depending on top highest value from other cell VBA-CodePudding

I have set from conditional formatting in vba to make cell color yellow if top 5 highest cost down (CD) amount. Now, I am stuck at Step (2) below. Where I'd like to input YES into column Propose? if the cell color is yellow. The rest will be input as NO. Actually this vba code can run if the cell is manually colored and not by conditional formatting by vba. Do you have any other way to do? Thank you in advance for your time.

Sub Sort ()
Dim R2 as Range    


'(1)Change cell to yellow if CD amount top 5 highest
Set R2 = range ("O18","O206")
R2.FormatConditions.Delete

R2.FormatConditions.AddTop10
With R2.FormatConditions(1)
.TopBottom.xlTop10Top
.Rank=5
End With



'(2) Input "YES" to Top 5 highest CD, the rest input "NO". Skip for blank rows
 Range("J18","J206").ClearContents
 For Each R2 in Range ("O18","O206")
 If R2.Cells.Interior.Color=RGB(255,255,0) Then
 Cells(R2.Row,"J").Value = "YES"
 Else
 Cells (R2.Row,"J").Value="NO"
 End If
 Next R2

 End Sub

Reference: Input "YES/NO" to a cell depending other column color

CodePudding user response：

If you insist using VBA: To check the color (or other properties) of a cell that is formatted using conditional formatting, use DisplayFormat, eg R2.DisplayFormat.Interior.Color, see https://stackoverflow.com/a/45123047/7599798

However, a simple formula (without VBA) can do the trick, use the Large-function.

=J2>=LARGE($O$18:$O$206, 5)

If you don't like TRUE/FALSE and prefer YES/NO, use

=IF(J2>=LARGE($O$18:$O$206, 5),"YES","NO")

CodePudding user response：

I managed to do the step (2) as below :

(2) Input YES to column J if range in column O is yellow
For each R2 in range ("O18","O206")
    If R2.Cells.Interior.Color=RGB (255,255,0) Then
    Cells(R2.Row,"J").Value = "YES"
    Else
    Cells(R2.Row,"J").Value="NO"
    End If
 Next R2