0

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.

enter image description here

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

zaidi ila
  • 3
  • 2
  • 1
    If you use conditionnal formating to do the color, why don't you use it to also do the Yes/No? – Vincent G Dec 13 '21 at 09:20
  • Because the VBA code cant detect the yellow color from the vba conditional formatting. For the code i wrote above. All the cell will input as NO since the code see the cell as has no color. – zaidi ila Dec 13 '21 at 09:23
  • 1
    I mean, why using VBA at all? – Vincent G Dec 13 '21 at 09:28
  • Because I need to automate this step for other user to use. The YES will acts as initial proposal. Actually, there will be other step after this question. My aim is to give the user the initial proposal. Unsure if you get what i'm trying to say or not :) – zaidi ila Dec 13 '21 at 09:36
  • Once you apply the conditional formatting, if the values change, the colors will change, but if you don't execute again the vba code, the YES/NO values won't automatically change. So IMHO it's best to either be fully volatile, with values changing in real time, as with FunThomas's solution, or fully static, not using conditional formatting at all. – Vincent G Dec 13 '21 at 09:43
  • True. @VincentG :) However, for the current moment, this is the best way that I can do to automate the task. Anwy thanks a lot for your time – zaidi ila Dec 14 '21 at 01:03

2 Answers2

2

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")
FunThomas
  • 23,043
  • 3
  • 18
  • 34
  • Thanks @FunThomas .Actually, I'd like to avoid plug in formula in the cell as i will not locked this cell. And the large function, i think it can just select the Nth highest value whereby I'd like to have the YES to the top 5 highest value. And as the color, i dont have issue on that I think. Anwy, thnks for your time to look into my question. – zaidi ila Dec 13 '21 at 10:07
0

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
zaidi ila
  • 3
  • 2