Forum Discussion

Afnas's avatar
Afnas
Copper Contributor
Jan 26, 2024

How to Bring a cell value to another sheet if it is only marked to colour yellow

If want to bring a value from cell H14 from my sheet named "Expense" to the sheet named "Consolidated" to the cell D14, if the "Expense" H14 is made only yellow colour. which i can mark and expense done and goes to the consolidated sheet.

 

How do i do this through excel formula

excel
Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jan 26, 2024

    Afnas 

    The easiest way to achieve this is to use a Visual Basic for Applications (VBA) macro. You can use VBA to create custom automations in Excel. Here is an example of simple VBA code that checks whether cell H14 in the Expense table is yellow, and if so, copies its value to cell D14 in the Consolidated table:

    Sub CopyYellowCellToConsolidated()
    Dim expenseSheet As Worksheet
    Dim consolidatedSheet As Worksheet
    Dim expenseCell As Range
    
    ' Set references to the sheets
    Set expenseSheet = Worksheets("Expense")
    Set consolidatedSheet = Worksheets("Consolidated")
    
    ' Set reference to the cell in the Expense sheet
    Set expenseCell = expenseSheet.Range("H14")
    
    ' Check if the cell is yellow
    If expenseCell.Interior.Color = RGB(255, 255, 0) Then
        ' Copy the value to the Consolidated sheet
        consolidatedSheet.Range("D14").Value = expenseCell.Value
    Else
        MsgBox "Expense is not marked as yellow.", vbExclamation
    End If
End Sub

    Here is how to use this code:

    1. Press Alt + F11 to open the VBA editor.
    2. Insert a new module by right-clicking on any item in the project explorer, selecting "Insert," and then choosing "Module."
    3. Copy and paste the provided VBA code into the module.
    4. Close the VBA editor.

    Now, you can run this macro manually whenever you want to copy the value from "Expense" H14 to "Consolidated" D14, but only if the cell is yellow.

    To run the macro:

    1. Press Alt + F8 to open the "Macro" dialog.
    2. Select "CopyYellowCellToConsolidated" and click "Run."

    Remember, this is a basic example, and you might need to adjust the code based on your specific requirements. Also, keep in mind that running macros in Excel requires enabling macros in your workbook, and the workbook needs to be saved as a macro-enabled workbook with the extension ".xlsm." The text, steps and code were created with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

Resources