Conditional formatting using option buttons or command button

[TimBrVa]

Hi,
I am relatively new to VBA coding but getting the hang of it. I am making a database to log new contacts for work. I am using a userform to input my data and would like to know if its possible to change the conditional formatting of my rows based on the sales process by using a separate "change status form". I was thinking of a search function followed with either option buttons or command buttons. to select which color of formatting.
Example of status changes
New Contact - green
Call Back - Yellow
Schedule Demo - Orange
Handed Off - Gray
Closed - Blue

Any help is greatly appreciated'
Thanks - Tim

 

[Micron]

Aren't the status values part of the sheet row somewhere, in which case you could have a cf rule (with 5 conditions) in the sheet cells? Then you don't need code.
You could code it but I don't have much to suggest because I'm not getting the whole picture. I can't see why you'd need another form that presumably shows the same data that the input form shows, just so you can colour rows. Could you not just use the same form? Or why a search needs to be involved. And doing it manually may be "safe" but it's not as certain as if it were automatic.

 

[TimBrVa]

Aren't the status values part of the sheet row somewhere, in which case you could have a cf rule (with 5 conditions) in the sheet cells? Then you don't need code.
You could code it but I don't have much to suggest because I'm not getting the whole picture. I can't see why you'd need another form that presumably shows the same data that the input form shows, just so you can colour rows. Could you not just use the same form? Or why a search needs to be involved. And doing it manually may be "safe" but it's not as certain as if it were automatic.

I appreciate the feedback. Lets say i have a database of 50 lines of contacts, I was hoping on being able to launch a form to search for a customer then click a button to change the color code based on the need, aka "Call Back:" or "Schedule Demo". From there, use a simple sort function to sort by color status. The main thing is my boss is not very tech saavvy, especially with excel so for him to scroll through the sheet then use standard conditional formatting, would be like me going to the moon.

 

[Micron]

We seem to be talking about different approaches. I'm saying you do something to initiate colouring your rows (maybe a button click) then code just finds "Call Back" in some column and colours the row light blue because that's what it should be. If it finds "Schedule Demo" it would colour the row yellow because that's what it should be. And so on. But I guess that is not what you want to do. Yes I know I've not spec'd the right colours for the status' but that's not important now.

launch a form to search for a customer

This part is unclear. You're thinking to pass form data to a sheet and set the row colour according to an option you select at that time? That means in order to change it in the future you need to be able to load row data into a (or the same) form. So I guess you have that covered off except your idea is to use a second form for the options. I don't see a need for that and view it as extra design work and maintenance. In that case you could add an option group to your form and based on the chosen option, colour the row. That assumes there can only be one row for a customer/entity. If not true, your form should load the row number that it gets the data from or puts it into. To determine which option was chosen and what to do for it, I would use a Select Case block and not a bunch of IF's to colour the row. I also expect that you need to control when an option can be chosen, such as only when x (or all) form fields have been filled in.

 

[TimBrVa]

We seem to be talking about different approaches. I'm saying you do something to initiate colouring your rows (maybe a button click) then code just finds "Call Back" in some column and colours the row light blue because that's what it should be. If it finds "Schedule Demo" it would colour the row yellow because that's what it should be. And so on. But I guess that is not what you want to do. Yes I know I've not spec'd the right colours for the status' but that's not important now.

This part is unclear. You're thinking to pass form data to a sheet and set the row colour according to an option you select at that time? That means in order to change it in the future you need to be able to load row data into a (or the same) form. So I guess you have that covered off except your idea is to use a second form for the options. I don't see a need for that and view it as extra design work and maintenance. In that case you could add an option group to your form and based on the chosen option, colour the row. That assumes there can only be one row for a customer/entity. If not true, your form should load the row number that it gets the data from or puts it into. To determine which option was chosen and what to do for it, I would use a Select Case block and not a bunch of IF's to colour the row. I also expect that you need to control when an option can be chosen, such as only when x (or all) form fields have been filled in.

Yessir, we getting closer. the rows will be one row with 10 columns (A-K) per customer. at any given time we need to be able to change the row color based on status (column A). i thought the easiest without having to scroll through all the data would be a search then format the search results. i attached a copy, Thanks

 

[Micron]

Seems over the top to colour the whole row rather than column A as you show. JMO but it usually just makes text harder to read at some point, and likely does anyone ever consider those who are colour blind (not making a joke here). I guess that before you can make any headway you need to modify your form (or create the second one) so that you can enter the status value in column A

 

[TimBrVa]

Seems over the top to colour the whole row rather than column A as you show. JMO but it usually just makes text harder to read at some point, and likely does anyone ever consider those who are colour blind (not making a joke here). I guess that before you can make any headway you need to modify your form (or create the second one) so that you can enter the status value in column A

this was already complete. I wrote the code to add "New" every time a new submission was entered , as i will do the same eventually when i have a command button to color the rows.

Dim lr As Long

lr = LSH.Cells(Rows.Count, 1).End(xlUp).Row

If LSH.Cells(1, "B").Value >= 1 Then
LSH.Cells(1, "A") = "New"
End If

 

[Micron]

Well I still don't have enough information to help you with that much. Perhaps you can make some use of how I might approach it. I had to put words in to show that coloring applies to the row you're on up to the last column with data (Lcol) because that is still unclear.

Dim Lcol As Long

Lcol = Sheets("LSH").Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

If LSH.Cells(1, "B").Value >= 1 Then
   LSH.Cells(1, "A") = "New"
   Select Case LSH.Cells(1, "A")
      Case "New" 'green
         Range(row,column that you're on:row,Lcol).Interior.Color = vbGreen
      Case "Call Back" 'yellow
         'rinse and repeat as for green
      Case "Demo" 'orange
         
      Case "Handed Off" 'Gray
         
      Case "Closed" 'Blue

   End Select
End If

 

[TimBrVa]

See if this helps..