Populate cell if row is not blank

sparkytech

Board Regular
Joined
Mar 6, 2018
Messages
94
Office Version
  1. 365
  2. 2019
I have a simple VBA request that I need help with. I want to fill cells down in column "AA" starting at row 5 with "X" if any of the cells B:F are not blank. For example, if any of the cells in B5 to F5 have data, fill AA5 with an "X." If row 10 (B10 to F10) is completely blank, clear cell AA10.

As a bonus, I would like to clear the "X" in each row (column AA) in realtime if anything in the same row (cells B:Y) are changed by the user.

Basically, column "AA" is a flag to conditional format / highlight rows when changes are made to the row, although I don't want or need the conditional format in VBA, so that it remains "mobile friendly". Its setup right now so that if a row doesn't have an "X" in AA, it highlights the row.

Can someone help with this? Thanks!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,693
Office Version
  1. 365
Platform
  1. Windows
If you want it to work in real time, why not just put a formula in col AA?
 

sparkytech

Board Regular
Joined
Mar 6, 2018
Messages
94
Office Version
  1. 365
  2. 2019
If you want it to work in real time, why not just put a formula in col AA?
I thought of that, but I want the user to also be able to clear or input "X" in AA manually if they wanted.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,693
Office Version
  1. 365
Platform
  1. Windows
If you want it to be Mobile Friendly then you cannot use VBA.
 

sparkytech

Board Regular
Joined
Mar 6, 2018
Messages
94
Office Version
  1. 365
  2. 2019
If you want it to be Mobile Friendly then you cannot use VBA.
Understood. Column AA will be handled by VBA if on PC (and will be most of the time), or this field can also be manually changed if desired. The conditional format will work on either, and is the part I needed to work everywhere. The VBA is so that on PC I can have a button to clear the flags in "AA" (clearing the flags puts an "X" in column AA which removes the conditional format from all of the rows.) The reason the flag is backwards from what I would normally do, is that I want any new rows that are added to the sheet to be highlighted without VBA (no "X" in AA will conditional format / highlight).
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,693
Office Version
  1. 365
Platform
  1. Windows
If you want it mobile friendly & for the X to change whenever the data changes, you need a formula.
 

sparkytech

Board Regular
Joined
Mar 6, 2018
Messages
94
Office Version
  1. 365
  2. 2019
If you want it mobile friendly & for the X to change whenever the data changes, you need a formula.
Thanks for the help! I think I got what I need for now, code is below, and seems to be working.

If anything in a row between columns "B" and "Y" is changed, this clears the "X" from cell "AA" in that row. That in turn triggers a Conditional Format to highlight the row yellow. I have a "Clear New Flags" button that triggers the "ClearNewFlags" macro to put an "X" in cell "AA" (my "new" flag cell) in each row. This triggers the Conditional Format to remove the yellow highlight from the row. If I am on mobile, the highlights work, and I could manually set cell "AA" if I wanted or needed to, in order to highlight new changes to a row.

This looks for any changes in the row, set cell “AA” in that row to blank (cell "AA" without an "X" triggers a Conditional Format to highlight row yellow)

Code in Sheet 1:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:Y")) Is Nothing Then _
Cells(Target.Row, "AA").Clear
End Sub

And my "ClearNewFlag" module linked to a button labeled “Clear New Flags”

VBA Code:
Sub ClearNewFlags()
If MsgBox("This will clear the 'New/Revised' flag, are you sure?", vbOKCancel) = vbOK Then
End If
Dim LastRow As Long
LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Range("AA5:AA" & LastRow) = "X"
End Sub

Thanks again for the assistance!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,693
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

sparkytech

Board Regular
Joined
Mar 6, 2018
Messages
94
Office Version
  1. 365
  2. 2019
Glad you sorted it & thanks for the feedback.
Thanks for the guidance. My apologies for my poor description of what I was trying to accomplish. 99.999% of the editing will be done on PC (utilizing VBA functions as well), mobile will only be used to view the data, so VBA isn't necessary there.
 

Forum statistics

Threads
1,181,426
Messages
5,929,822
Members
436,697
Latest member
sunnypl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top