• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional criteria with colored cells

fher9728

New Member
Hi community,

I've been doing some work in a table, and this table works a lot with dates, so the thing I wanted to do is that dates that are 15 days after todays date they'll be highlight in color yellow, I did that with no problems with conditional formatting, but the problem that I have is in the next step, I want that the cells that are highlight in yellow shows a Status that says "Vencida" and the cells that aren't highlight show status "Vigente", but I want to do this full with Vba, but without a vba function because I dont want to use formulas because the range of data is so extensive that the Workbook will be so big and I need to remain a fast and light workbook, I'll show the table:


1585148317119.png



The thing I want to do is like a conditional that if cells with range G are highlight with yellow color the status in range M shows me "Vencida" else show "Vigente",
I've been trying to do that but it only shows me Vigente, but the status doesn't change to Vencida, this is the code:

VBA Code:
Code:
Sub COUNT_HIGHLIGHTS()
'
' COUNT_HIGHLIGHTS
'


'Defining variables
Dim LastRow As Long, Count As String
Dim celdaOrigen As Range, rango As Range


'Getting a number value for the number of rows in the table (as this can vary on table size)
LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "G").End(xlUp).Row


'For loop to loop through rows
For i = 2 To LastRow
'Count is the number of cells in the row which are highlighted
Count = "VIGENTE"
'For loop for cells within the row (My table always has 36 cells)
For J = 2 To LastRow
'If statement to check if cell is highlighted
If Cells(i, 7).Interior.Color = ActiveSheet.Range("U1").Interior.Color Then
'Add +1 to count ever time a highlighted cell is found
Count = "VENCIDA"
End If
Next J
'find cell at the end of the row and add the count
Cells(i, 13).Select
Selection.Value = Count
Next i
End Sub
SECOND POINT


1585148673415.png


In column H,J,K,L I have an vba code which if I put any text or value in Column B range it will throw me automatically todays date, like registration date in the table, the problem that I have is that I need to copy paste data from another table and if do that it doesnt shows me automatically the registration date, I want to copy and paste the data un column B and automatically shows me the date in column G, also I want to let me undo the action because when I modify something in this two columns it doesn't let me undo any action:
1585148939568.png


the vba code for this is :
VBA Code:


Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Range("B:B")) Is Nothing Then

Range("H" & Target.Row) = Date

Range("I" & Target.Row) = Format(Now, "hh:mm")

End If

If Not Application.Intersect(Target, Range("J:J")) Is Nothing Then

Range("K" & Target.Row) = Date + Target.Value

End If

If Not Application.Intersect(Target, Range("K:K")) Is Nothing Then

Range("L" & Target.Row) = Target.Value

End If
End Sub
Pleaaase I need help, thankyou

>> Please, use code - tags as written in Forum Rules <<
 
Last edited by a moderator:
Hi,​
your first code has a lack of Logic and as it is written - loop - it could be slower than formulas in column​
so it's better - and easier to maintain for beginners - to use even under a VBA procedure a formula - without any loop -​
as a formula can be replaced by its result … (*)
For your second point, as soon as a VBA procedure is executed the Undo Excel feature is not available,​
Undo is only possible after a manual operation.​
So if you need to 'Undo' after a VBA procedure you must reload the workbook as it was before launching the procedure​
so you just must save the workbook before.​
For further help attach a workbook sample and for each point a complete technical explanation of the need​
with before and after contexts examples accordingly, in order there is nothing to guess for any helper …​
(*) Start with a formula in column and once it works, post it here then it will be easy to show you how to use it within a VBA procedure.
 
For point 1:
Code:
Sub AddStatus()
Dim LastRow As Long, myColour, i As Long

LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "G").End(xlUp).Row
myColour = ActiveSheet.Range("U1").Interior.Color
For i = 2 To LastRow
  With Cells(i, "M")
    If Cells(i, 7).DisplayFormat.Interior.Color = myColour Then .Value = "VENCIDA" Else .Value = "VIGENTE"
  End With
Next i
End Sub
If this is too slow, come back and I'll speed it up, but you'll need to give me the conditional formatting rule. Best attach a workbook, not pictures.
 
Last edited:
Grrrr.
You've cross posted without supplying links.
Wasted my time. You could've guessed, or at least checked the rules here too.
 
For point 1:
Code:
Sub AddStatus()
Dim LastRow As Long, myColour, i As Long

LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "G").End(xlUp).Row
myColour = ActiveSheet.Range("U1").Interior.Color
For i = 2 To LastRow
  With Cells(i, "M")
    If Cells(i, 7).DisplayFormat.Interior.Color = myColour Then .Value = "VENCIDA" Else .Value = "VIGENTE"
  End With
Next i
End Sub
If this is too slow, come back and I'll speed it up, but you'll need to give me the conditional formatting rule. Best attach a workbook, not pictures.
It works fine, thankyou so much.
 
Grrrr.
You've cross posted without supplying links.
Wasted my time. You could've guessed, or at least checked the rules here too.
Sorry I didn't knew about cross posts until yesterday, but you didn't waste your time I'm trying to analyze the different ways to structure a code for a case like this, thank you!
 
Back
Top