Use VBA to Find Precedent Cells Positioned After Dependents in WPS Spreadsheets
Question details
The user needs a VBA routine that scans formula cells in a large WPS Spreadsheets worksheet and isolates direct precedent cells that are physically positioned after the dependent cell.

- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The worksheet contains more than 200 columns and 6,000 rows with many formulas. The user wants to audit formula layout and identify precedent cells that appear later than the dependent formula cell.
- Observed behavior
- Existing arrow-based or generic VBA methods either color all precedents or become slow, so they do not isolate only the precedent cells that appear after their dependent cells.
Open the target workbook in WPS Spreadsheets and save it as a macro-enabled file such as .xlsm or .xls if you intend to keep the VBA code. Enable the Developer tab or use Alt+F11 to access the Visual Basic editor.
Highlight direct precedent cells that are positioned after their dependent formula cells
Use this macro when you need a reliable, cell-by-cell audit of formula position without leaving trace arrows on the worksheet.
This method uses the Range.Precedents property to read direct precedents and compares each precedent's row and column with the dependent cell in row-major order. It is more predictable than drawing ShowPrecedents arrows, but it only checks direct precedents on the same worksheet.
Open your workbook in WPS Spreadsheets, press Alt+F11 to open the Microsoft Visual Basic for Applications editor, or enable the Developer tab and click Visual Basic.
In the VBA editor, click Insert > Module to create Module1.
Paste the following code into the module: Sub HighlightPrecedentsAfterDependents() Dim ws As Worksheet Dim depCell As Range Dim prec As Range Dim area As Range Dim cell As Range Set ws = ActiveSheet Application.ScreenUpdating = False ws.Cells.Interior.ColorIndex = xlNone For Each depCell In ws.UsedRange If depCell.HasFormula Then On Error Resume Next Set prec = depCell.Precedents On Error GoTo 0 If Not prec Is Nothing Then For Each area In prec.Areas For Each cell In area.Cells If cell.Row > depCell.Row Or (cell.Row = depCell.Row And cell.Column > depCell.Column) Then cell.Interior.Color = vbYellow End If Next cell Next area End If Set prec = Nothing End If Next depCell Application.ScreenUpdating = True End Sub
Click anywhere inside the macro, press F5, or choose Run > Run Sub/UserForm, then return to WPS Spreadsheets.
Cells highlighted in yellow are direct precedents that appear after the dependent formula cell. Adjust the color or add a message box in the code if you prefer a list of cell addresses.

Find later-position precedents in WPS Spreadsheets with VBA
WPS Spreadsheets supports VBA macros, so you can run the same direct precedent scan to audit formula order and highlight problem cells on large worksheets without changing your Excel workflow.
- 1. Download and install WPS Office: Go to the WPS Office download page, download the free installer, and open WPS Spreadsheets.
- 2. Open the workbook: Open the .xlsm or .xlsx workbook containing the formulas you want to audit.
- 3. Access the VBA editor: Press Alt+F11 or click Developer > Visual Basic to open the macro editor.
- 4. Paste and run the macro: Insert a module, paste the position-check code, and run it to highlight later precedents.

Frequently Asked Questions
Why does my VBA code color every precedent instead of only later precedents?
Most arrow-drawing or ShowPrecedents snippets mark all precedents. Add a row and column comparison after reading Range.Precedents so only cells with a later row, or a greater column in the same row, are highlighted.
Does Range.Precedents work with formulas that refer to other worksheets?
Range.Precedents returns direct precedents on the same sheet. For external or closed-workbook references, the property may fail or return limited results, so wrap that part in On Error Resume Next and run the scan on local formulas first.
Can I list the problem cells in a message box instead of coloring them?
Yes. In the loop, build a string such as cell.Address and display it with MsgBox after scanning, while keeping the same row and column comparison logic.
Will this macro work in WPS Spreadsheets as well as Microsoft Excel?
WPS Spreadsheets supports VBA macros on Windows. Open the editor with Alt+F11, insert a module, and use the same Range.Precedents comparison code.
How do I prevent the macro from slowing down a 6,000-row worksheet?
Set Application.ScreenUpdating to False before scanning and restore it afterward. Avoid ShowPrecedents arrows and use direct Range.Precedents checks only on cells that have formulas.




